... Spooshe : That's a wonderful code given by Lintz
Here is another one... which will give you the result in Years, Months and Days...
Private Sub Command1_Click()
'For example
MsgBox Spooshe(#4/24/2005#, #2/24/2007#, True, True, True)
End Sub
Public Function Spooshe(StartDate As Date, EndDate As Date, Optional WithMonths As Boolean = False, _
Optional WithDays As Boolean = False, Optional DisplayWithWords As Boolean = False) As Variant
On Error GoTo Spooshe_ErrorHandler
Dim iYears As Integer
Dim iMonths As Integer
Dim iDays As Integer
Dim dTempDate As Date
'Check that the dates are valid
If Not (IsDate(StartDate)) Or Not (IsDate(EndDate)) Then
DoCmd.Beep
MsgBox "Invalid date.", vbOKOnly + vbInformation, "Invalid date"
GoTo Spooshe_ErrorHandler
End If
'Check that StartDate < EndDate
If StartDate > EndDate Then
DoCmd.Beep
MsgBox "EndDate must be greater than StartDate.", _
vbOKOnly + vbInformation, "Invalid date position"
GoTo Spooshe_ErrorHandler
End If
iYears = DateDiff("yyyy", StartDate, EndDate) - _
IIf(DateAdd("yyyy", DateDiff("yyyy", StartDate, EndDate), StartDate) > EndDate, 1, 0)
dTempDate = DateAdd("yyyy", iYears, StartDate)
If WithMonths Then
iMonths = DateDiff("m", dTempDate, EndDate) - _
IIf(DateAdd("m", iMonths, DateAdd("yyyy", iYears, StartDate)) > EndDate, 1, 0)
dTempDate = DateAdd("m", iMonths, dTempDate)
End If
If WithDays Then
iDays = EndDate - dTempDate
End If
'Format the output
If DisplayWithWords Then
'Display the output in words
Spooshe = IIf(iYears > 0, iYears & " year" & IIf(iYears <> 1, "s ", " "), "")
Spooshe = Spooshe & IIf(WithMonths, iMonths & " month" & IIf(iMonths <> 1, "s ", " "), "")
Spooshe = Trim(Spooshe & IIf(WithDays, iDays & " day" & IIf(iDays <> 1, "s", ""), ""))
Else
'Display the output in the format yy.mm.dd
Spooshe = Trim(iYears & IIf(WithMonths, "." & Format(iMonths, "00"), "") _
& IIf(WithDays, "." & Format(iDays, "00"), ""))
End If
Exit_Spooshe:
Exit Function
Spooshe_ErrorHandler:
Spooshe = Null
Resume Exit_Spooshe
End Function
Hope this helps...