Here is a date diff function I found on the web, covers a lot different outputs:-
?Diff2Dates("y", #06/01/1998#, #06/26/2002#)
4 years
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#)
4 years 25 days
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#, True)
4 years 0 months 25 days
?Diff2Dates("d", #06/01/1998#, #06/26/2002#)
1486 days
?Diff2Dates("h", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
42 hours
?Diff2Dates("hns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
42 hours 47 minutes 33 seconds
?Diff2Dates("dhns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
1 day 18 hours 47 minutes 33 seconds
?Diff2Dates("ymd",#12/31/1999#,#1/1/2000#)
1 day
?Diff2Dates("ymd",#1/1/2000#,#12/31/1999#)
-1 day
?Diff2Dates("ymd",#1/1/2000#,#1/2/2000#)
1 day
--------------------------------------------------------------------------------
Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As Date, _
Optional ShowZero As Boolean = False) As Variant
'Authors: © Copyright 2001.
' Graham R Seach MCP MVP
gseach@pacificdb.com.au
' Douglas J. Steele MVP
'
' You may freely use and distribute this code
' with any applications you may develop, on the
' condition that the copyright notice remains
' unchanged, and intact as part of the code. You
' may not sell or publish this code in any form
' without the express written permission of the
' copyright holders.
'
'Description: This function calculates the number of years,
' months, days, hours, minutes and seconds between
' two dates, as elapsed time.
'
'Inputs: Interval: Intervals to be displayed (a string)
' Date1: The lower date (see below)
' Date2: The higher date (see below)
' ShowZero: Boolean to select showing zero elements
'
'Outputs: On error: Null
' On no error: Variant containing the number of years,
' months, days, hours, minutes & seconds between
' the two dates, depending on the display interval
' selected.
' If Date1 is greater than Date2, the result will
' be a negative value.
' The function compensates for the lack of any intervals
' not listed. For example, if Interval lists "m", but
' not "y", the function adds the value of the year
' component to the month component.
' If ShowZero is True, and an output element is zero, it
' is displayed. However, if ShowZero is False or
' omitted, no zero-value elements are displayed.
' For example, with ShowZero = False, Interval = "ym",
' elements = 0 & 1 respectively, the output string
' will be "1 month" - not "0 years 1 month".
' Additional changes:
' 1) Formats associated with date segments.
' 2) Changed order of arguments.
' 3) Variables renamed. Constants replaced by text.
' Output sign detection changed (+ or -).
' Comments added. Copyright changed.
On Error GoTo Diff2Dates_ErrorHandler
Dim vTemp As Variant 'A general purpose variant
Dim lYearDiff As Long 'Difference in years
Dim lMonthDiff As Long 'Difference in months
Dim lDayDiff As Long 'Difference in days
Dim lHourDiff As Long 'Difference in hours
Dim lMinDiff As Long 'Difference in minutes
Dim lSecDiff As Long 'Difference in seconds
Dim bYear As Boolean 'Boolean - Was year output selected?
Dim bMonth As Boolean 'Boolean - Was month output selected?
Dim bDay As Boolean 'Boolean - Was day output selected?
Dim bHour As Boolean 'Boolean - Was hour output selected?
Dim bMinute As Boolean 'Boolean - Was minute output selected?
Dim bSecond As Boolean 'Boolean - Was second output selected?
Dim iCtr As Integer 'General purpose counter
Dim dTmpDate As Date 'A temporary date container
Dim bSwapped As Boolean 'Boolean flag indicating whether the dates have been swapped
Const INTERVALS As String = "dmyhns"
'Check that Interval contains valid characters
Interval = Trim(LCase(Interval))
For iCtr = 1 To Len(Interval)
If InStr(1, INTERVALS, Mid(Interval, iCtr, 1)) = 0 Then
GoTo Diff2Dates_ErrorHandler
End If
Next iCtr
'Check that valid dates have been entered
If Not (IsDate(Date1)) Then Exit Function
If Not (IsDate(Date2)) Then Exit Function
'If necessary, swap the dates, to ensure that
'Date1 is lower than Date2
If Date1 > Date2 Then
dTmpDate = Date1
Date1 = Date2
Date2 = dTmpDate
bSwapped = True
End If
vTemp = Null
'What intervals are supplied
bYear = (InStr(1, Interval, "y") > 0)
bMonth = (InStr(1, Interval, "m") > 0)
bDay = (InStr(1, Interval, "d") > 0)
bHour = (InStr(1, Interval, "h") > 0)
bMinute = (InStr(1, Interval, "n") > 0)
bSecond = (InStr(1, Interval, "s") > 0)
'Debug.Print "Date1: " & Date1
'Debug.Print "Date2: " & Date2
'************************************************************
'Get the cumulative differences between the two dates
If bYear Then
lYearDiff = Abs(DateDiff("yyyy", Date1, Date2) - _
IIf(Format(Date1, "mmddhhnnss") <= Format(Date2, "mmddhhnnss"), 0, 1))
Date1 = DateAdd("yyyy", lYearDiff, Date1)
End If
If bMonth Then
lMonthDiff = Abs(DateDiff("m", Date1, Date2)) - _
IIf(Format(Date1, "ddhhnnss") <= Format(Date2, "ddhhnnss"), 0, 1)
Date1 = DateAdd("m", lMonthDiff, Date1)
End If
If bDay Then
lDayDiff = Abs(DateDiff("d", Date1, Date2)) - _
IIf(Format(Date1, "hhnnss") <= Format(Date2, "hhnnss"), 0, 1)
Date1 = DateAdd("d", lDayDiff, Date1)
End If
If bHour Then
lHourDiff = Abs(DateDiff("h", Date1, Date2)) - _
IIf(Format(Date1, "nnss") <= Format(Date2, "nnss"), 0, 1)
Date1 = DateAdd("h", lHourDiff, Date1)
End If
If bMinute Then
lMinDiff = Abs(DateDiff("n", Date1, Date2)) - _
IIf(Format(Date1, "ss") <= Format(Date2, "ss"), 0, 1)
Date1 = DateAdd("n", lMinDiff, Date1)
End If
If bSecond Then
lSecDiff = Abs(DateDiff("s", Date1, Date2))
Date1 = DateAdd("s", lSecDiff, Date1)
End If
'************************************************************
'Set the output display
If bYear And (lYearDiff > 0 Or ShowZero) Then
vTemp = lYearDiff & IIf(lYearDiff <> 1, " years", " year")
End If
If bMonth And (lMonthDiff > 0 Or ShowZero) Then
If bMonth Then
vTemp = vTemp & IIf(IsNull(vTemp), Null, " ") & _
lMonthDiff & IIf(lMonthDiff <> 1, " months", " month")
End If
End If
If bDay And (lDayDiff > 0 Or ShowZero) Then
If bDay Then
vTemp = vTemp & IIf(IsNull(vTemp), Null, " ") & _
lDayDiff & IIf(lDayDiff <> 1, " days", " day")
End If
End If
If bHour And (lHourDiff > 0 Or ShowZero) Then
If bHour Then
vTemp = vTemp & IIf(IsNull(vTemp), Null, " ") & _
lHourDiff & IIf(lHourDiff <> 1, " hours", " hour")
End If
End If
If bMinute And (lMinDiff > 0 Or ShowZero) Then
If bMinute Then
vTemp = vTemp & IIf(IsNull(vTemp), Null, " ") & _
lMinDiff & IIf(lMinDiff <> 1, " minutes", " minute")
End If
End If
If bSecond And (lSecDiff > 0 Or ShowZero) Then
If bSecond Then
vTemp = vTemp & IIf(IsNull(vTemp), Null, " ") & _
lSecDiff & IIf(lSecDiff <> 1, " seconds", " second")
End If
End If
'************************************************************
If bSwapped Then vTemp = "-" & vTemp
Diff2Dates = Trim(vTemp)
Debug.Print vTemp
Debug.Print Trim(vTemp)
Exit_Diff2Dates:
Exit Function
Diff2Dates_ErrorHandler:
Diff2Dates = Null
End Function
