Date difference in years, months, days

P

plannerg

Registered User.
Local time
Today, 13:21
Joined
Oct 19, 2005
Messages
23
Hello,

I have two fields in a table, both containing dates. I would like to write a query that returns the difference between the dates in an year, month, day format (i.e. 26y 2m 3d). If that isnt possible, can the difference be returned as years,days (i.e. 26y 63d).

Thankyou.
 
It may not be the most elegant way to do this but :

Create a field in your query something like :
VersY: DateDiff("yyyy",[startdate],[enddate])
and next field :
VersM: DateDiff("m",[startdate],[enddate])
and next field :
VersD: DateDiff("d",[startdate],[enddate])

Than create a field :
Total: [VersY] & 'Y ' & [VersM] & 'm ' & [VersD] & 'd '

This will give you something like :
startdate enddate VersY VersM VersD Total
1/1/2003 4/1/2007 4 51 1551 4Y 51m 1551d

Hth
 
Hi -

See if this provides what you're after:
Code: 
Function fAge(dteStart As Variant, dteEnd As Variant) As Variant
'*******************************************
'Purpose:   Accurately return the difference
'           between two dates, expressed as
'           years.months.days
'Coded by:  raskew
'Inputs:    From debug (immediate) window
'           1) ? fAge(#12/1/1950#, #8/31/2006#)
'           2) ? fAge(#12/30/2005#, #1/2/2006#)
'Outputs:   1) 55.8.30
'           2) 0.0.3
'*******************************************

Dim intHold   As Integer
Dim dayhold   As Integer


   'correctly return number of whole months difference
   'the (Day(dteEnd) < Day(dteStart)) is a Boolean statement
   'that returns -1 if true, 0 if false
   intHold = DateDiff("m", dteStart, dteEnd) + (Day(dteEnd) < Day(dteStart))
   
   'correctly return number of days difference
   If Day(dteEnd) < Day(dteStart) Then
      dayhold = DateDiff("d", dteStart, DateSerial(year(dteStart), month(dteStart) + 1, 0)) + Day(dteEnd)
   Else
      dayhold = Day(dteEnd) - Day(dteStart)
   End If
   
   fAge = LTrim(str(intHold \ 12)) & "." & LTrim(str(intHold Mod 12)) & "." & LTrim(str(dayhold))

End Function

HTH - Bob
 
rak said:
It may not be the most elegant way to do this but :

Create a field in your query something like :
VersY: DateDiff("yyyy",[startdate],[enddate])
and next field :
VersM: DateDiff("m",[startdate],[enddate])
and next field :
VersD: DateDiff("d",[startdate],[enddate])

Than create a field :
Total: [VersY] & 'Y ' & [VersM] & 'm ' & [VersD] & 'd '

This will give you something like :
startdate enddate VersY VersM VersD Total
1/1/2003 4/1/2007 4 51 1551 4Y 51m 1551d

Hth
Click to expand...



rak, thanks for that however I was hoping it would return 4Y 0m 3d. Is that possible?
 
Easy enough, but use the American formatting (mm/dd/yy).

Year(#1/1/2007#) - Year(#1/4/2003#) = 4
Month(#1/1/2007#) - Month(#1/4/2003#) = 0
Day(#1/4/2007#) - Day(#1/1/2003#) = 3

You can format your dates with a FORMAT Function.
 
How would you modify this to account for:
StartDate and EndDate is blank / null? Right now it shows "#Type!" if this occurs.

If just the start date is populated but no end date it also shows "#Type!"

If the value of the math = 1 year, 0 months, 0 days
How to change it so, it just shows the 1 year?

Same logic if the year or the day is 0. No point in showing that segment of data.

raskew said:
Hi -

See if this provides what you're after:
Code: 
Function fAge(dteStart As Variant, dteEnd As Variant) As Variant
'*******************************************
'Purpose:   Accurately return the difference
'           between two dates, expressed as
'           years.months.days
'Coded by:  raskew
'Inputs:    From debug (immediate) window
'           1) ? fAge(#12/1/1950#, #8/31/2006#)
'           2) ? fAge(#12/30/2005#, #1/2/2006#)
'Outputs:   1) 55.8.30
'           2) 0.0.3
'*******************************************

Dim intHold   As Integer
Dim dayhold   As Integer


   'correctly return number of whole months difference
   'the (Day(dteEnd) < Day(dteStart)) is a Boolean statement
   'that returns -1 if true, 0 if false
   intHold = DateDiff("m", dteStart, dteEnd) + (Day(dteEnd) < Day(dteStart))
   
   'correctly return number of days difference
   If Day(dteEnd) < Day(dteStart) Then
      dayhold = DateDiff("d", dteStart, DateSerial(year(dteStart), month(dteStart) + 1, 0)) + Day(dteEnd)
   Else
      dayhold = Day(dteEnd) - Day(dteStart)
   End If
   
   fAge = LTrim(str(intHold \ 12)) & "." & LTrim(str(intHold Mod 12)) & "." & LTrim(str(dayhold))

End Function

HTH - Bob
Click to expand...
 
dgreen said:
How would you modify this to account for:
StartDate and EndDate is blank / null? Right now it shows "#Type!" if this occurs.

If just the start date is populated but no end date it also shows "#Type!"

If the value of the math = 1 year, 0 months, 0 days
How to change it so, it just shows the 1 year?

Same logic if the year or the day is 0. No point in showing that segment of data.
Click to expand...
Hi. This is a 13-yr old thread. You might consider starting a new one with a link here. In any case, you can modify the code to test first if the arguments are null and then act accordingly. Cheers!
 
how about using my Age() function
Code: 
' agp
Public Function Age(Date1 As Date, Optional Date2 As Date = 0) As String
Dim Year1 As Integer
Dim Month_1 As Integer
Dim Day1 As Integer
Dim temp As Date
If Date2 = 0 Then Date2 = Date
temp = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Year1 = Year(Date2) - Year(Date1) + (temp > Date2)
Month_1 = Month(Date2) - Month(Date1) - (12 * (temp > Date2))
Day1 = Day(Date2) - Day(Date1)
If Day1 < 0 Then
    Month_1 = Month_1 - 1
    Day1 = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + Day1 + 1
End If
If Year1 > 0 Then
    Age = Year1
    If Year1 > 1 Then
        Age = Age & " years "
    Else
        Age = Age & " year "
    End If
    If Month_1 > 0 And Day1 = 0 Then
        Age = Age & " and "
    End If
End If
If Month_1 > 0 Then
    Age = Age & Month_1
    If Month_1 > 1 Then
        Age = Age & " months "
    Else
        Age = Age & " month "
    End If
End If
If Day1 > 0 Then
    Age = Age & " and " & Day1
    If Day1 > 1 Then
        Age = Age & " days"
    Else
        Age = Age & " day"
    End If
End If
Age = Trim(Replace(Age, "  ", " "))
End Function
 
@arnelgp That's exactly what I was looking for. Thanks. I've implemented it.
Now how would you change it to handle just having a start date or just an end date? Currently you get an error "#Type!"
 
Last edited:
you would always pass a Date to Date1 parameter since how can you compute the Age if Date1 (DOB) is not given.
if the parameter Date2 is Null, we add additional code to the function:

Code: 
' agp
Public Function Age(Date1 As Date, Optional Date2 As Variant = 0) As String
Dim Year1 As Integer
Dim Month_1 As Integer
Dim Day1 As Integer
Dim temp As Date
If IsNull(Date2) Then Date2 = Date
If Date2 = 0 Then Date2 = Date
temp = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Year1 = Year(Date2) - Year(Date1) + (temp > Date2)
Month_1 = Month(Date2) - Month(Date1) - (12 * (temp > Date2))
Day1 = Day(Date2) - Day(Date1)
If Day1 < 0 Then
    Month_1 = Month_1 - 1
    Day1 = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + Day1 + 1
End If
If Year1 > 0 Then
    Age = Year1
    If Year1 > 1 Then
        Age = Age & " years "
    Else
        Age = Age & " year "
    End If
    If Month_1 > 0 And Day1 = 0 Then
        Age = Age & " and "
    End If
End If
If Month_1 > 0 Then
    Age = Age & Month_1
    If Month_1 > 1 Then
        Age = Age & " months "
    Else
        Age = Age & " month "
    End If
End If
If Day1 > 0 Then
    Age = Age & " and " & Day1
    If Day1 > 1 Then
        Age = Age & " days"
    Else
        Age = Age & " day"
    End If
End If
Age = Trim(Replace(Age, "  ", " "))
End Function
 
Thanks. In some cases, you dont have that value at the time of entry.
 
@arnelgp

If the date is within a month, the current result is "and, # days". I'd recommend changing the result to just "# days"
Also, if you just know when someone died (e.g. the optional date2), the result is "#Type!". I'd recommend changing to a " " to provide an empty result.
 
Thanks. In some cases, you dont have that value at the time of entry.
Click to expand...
I updated the code. it will return "Unknown" for unspecified date of birth.
also, removed "and" when only number of days are displayed.
please test again.
Code: 
' agp
Public Function Age(Date1 As Variant, Optional Date2 As Variant = 0) As String
Dim Year1 As Integer
Dim Month_1 As Integer
Dim Day1 As Integer
Dim temp As Date
Dim sAge As String

Age = "Unknown"

If Trim(Date1 & "") = "" Then Exit Function
If IsDate(Date1) = False Then Exit Function

Age = ""

If Date2 = 0 Then Date2 = Date
temp = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Year1 = Year(Date2) - Year(Date1) + (temp > Date2)
Month_1 = Month(Date2) - Month(Date1) - (12 * (temp > Date2))
Day1 = Day(Date2) - Day(Date1)
If Day1 < 0 Then
    Month_1 = Month_1 - 1
    Day1 = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + Day1 + 1
End If
If Year1 > 0 Then
    Age = Year1
    If Year1 > 1 Then
        Age = Age & " years "
    Else
        Age = Age & " year "
    End If
    If Month_1 > 0 And Day1 = 0 Then
        Age = Age & " and "
    End If
End If
If Month_1 > 0 Then
    Age = Age & Month_1
    If Month_1 > 1 Then
        Age = Age & " months "
    Else
        Age = Age & " month "
    End If
End If
If Day1 > 0 Then
    Age = Age & " and " & Day1
    If Day1 > 1 Then
        Age = Age & " days"
    Else
        Age = Age & " day"
    End If
End If
sAge = Trim(Replace(Age, "  ", " "))
If InStr(sAge, "and") = 1 Then _
    sAge = Trim(Replace(sAge, "and", ""))
Age = sAge
End Function
 
Thank you

arnelgp said:
I updated the code. it will return "Unknown" for unspecified date of birth.
also, removed "and" when only number of days are displayed.
please test again.
Code: 
' agp
Public Function Age(Date1 As Variant, Optional Date2 As Variant = 0) As String
Dim Year1 As Integer
Dim Month_1 As Integer
Dim Day1 As Integer
Dim temp As Date
Dim sAge As String

Age = "Unknown"

If Trim(Date1 & "") = "" Then Exit Function
If IsDate(Date1) = False Then Exit Function

Age = ""

If Date2 = 0 Then Date2 = Date
temp = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Year1 = Year(Date2) - Year(Date1) + (temp > Date2)
Month_1 = Month(Date2) - Month(Date1) - (12 * (temp > Date2))
Day1 = Day(Date2) - Day(Date1)
If Day1 < 0 Then
    Month_1 = Month_1 - 1
    Day1 = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + Day1 + 1
End If
If Year1 > 0 Then
    Age = Year1
    If Year1 > 1 Then
        Age = Age & " years "
    Else
        Age = Age & " year "
    End If
    If Month_1 > 0 And Day1 = 0 Then
        Age = Age & " and "
    End If
End If
If Month_1 > 0 Then
    Age = Age & Month_1
    If Month_1 > 1 Then
        Age = Age & " months "
    Else
        Age = Age & " month "
    End If
End If
If Day1 > 0 Then
    Age = Age & " and " & Day1
    If Day1 > 1 Then
        Age = Age & " days"
    Else
        Age = Age & " day"
    End If
End If
sAge = Trim(Replace(Age, "  ", " "))
If InStr(sAge, "and") = 1 Then _
    sAge = Trim(Replace(sAge, "and", ""))
Age = sAge
End Function
Click to expand...
 
you're welcome and please test it more times.
 
@arnelgp
Error occurring on situation where I just have date 1 (start date) and no date 2 (end date).
Invalid use of null on the below line of code within the Age = ""

Code: 
temp = DateSerial(Year(Date2), Month(Date1), Day(Date1))
 
I haven't figured out what is causing the Invalid Null mentioned above .... But I have started to comment the code.

Code: 
Option Compare Database
Option Explicit

'https://www.access-programmers.co.uk/forums/threads/date-difference-in-years-months-days.121140/#post-1674999
'Provided by @arnelgp

Public Function Age(Date1 As Variant, Optional Date2 As Variant = 0) As String
Dim Year1 As Integer
Dim Month_1 As Integer
Dim Day1 As Integer
Dim temp As Date
Dim sAge As String

'the result will be Unknown if Date1 is empty or is not in a date format
Age = "Unknown"

If Trim(Date1 & "") = "" Then Exit Function
If IsDate(Date1) = False Then Exit Function

'the result will be empty if .....
Age = ""

If Date2 = 0 Then Date2 = Date

temp = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Year1 = Year(Date2) - Year(Date1) + (temp > Date2)
Month_1 = Month(Date2) - Month(Date1) - (12 * (temp > Date2))

'Day in date2 minus date1, gives the # of days
Day1 = Day(Date2) - Day(Date1)

'if day1 is less than 0 then
If Day1 < 0 Then
    Month_1 = Month_1 - 1
    Day1 = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + Day1 + 1
End If

'if Year1 is greater than 0 then
If Year1 > 0 Then
    Age = Year1
    'if the year # is greater than 1 use plural
    If Year1 > 1 Then
        Age = Age & " years "
    
    'Otherwise use singluar
    Else
        Age = Age & " year "
    End If
    
    'if month_1 is greater than 0 and Day1 is equal to 0 then use below which will just join the year and month
    If Month_1 > 0 And Day1 = 0 Then
        Age = Age & " and "
    End If
End If

'if month_1 is greater than 0 and based on the above the days are more than 0 then
If Month_1 > 0 Then
    Age = Age & Month_1
    If Month_1 > 1 Then
        Age = Age & " months "
    Else
        Age = Age & " month "
    End If
End If

If Day1 > 0 Then
    Age = Age & " and " & Day1
    If Day1 > 1 Then
        Age = Age & " days"
    Else
        Age = Age & " day"
    End If
End If

sAge = Trim(Replace(Age, "  ", " "))

If InStr(sAge, "and") = 1 Then _
    sAge = Trim(Replace(sAge, "and", ""))
Age = sAge

End Function
 
If you have a case that produces "Invalid use of null" then don't fix it. Instead, put a breakpoint on the Age = "Unknown" line. Run the situation that gave you the error. When you hit the breakpoint, single step through the code (via F8 key) until you see where the null pops up.

Identifying the miscreant line will tell you which variables are accused of being null.
 
The miscreant line is below. In this case Date2 is null. What should be happening is then Date2 should equal today's date in the code above.
Code: 
temp = DateSerial(Year(Date2), Month(Date1), Day(Date1))
 
You must log in or register to reply here.

Similar threads

mloucel
Solved Interesting QUERY problem
Replies
11
Views
744
mloucel
mloucel
C
limit date formula as variable
Replies
16
Views
1,421
MajP
MajP
Luclarie
DLAST FOR DATE SERIAL
Replies
19
Views
666
Ken Sheridan
Ken Sheridan
Gasman
Solved Query advice please
Replies
7
Views
503
Gasman
Gasman
J
Default Date in Textbox
Replies
3
Views
935
Papa_Bear1
Papa_Bear1

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom