Par2: Months between dates 2002-08-23 -- Michael Ware For just the number of months between dates:
12 * (Year(Dt1) - Year(Dt2)) + Month(Dt1) - Month(Dt2)
If you're looking for fractional months (i.e. in your example March 20th is
about 2/3 into the month) it's more complicated, something like:
12*(Year(Dt1)-Year(Dt2)) + Month(Dt1) - Month(Dt2) +
(DAY(Dt1)/DAY(DATE(Month(Dt1)+1,1,YEAR(Dt1))+1)) -
(DAY(Dt2)/DAY(DATE(Month(Dt2)+1,1,YEAR(Dt2))+1))
Carl Barnes adds:
You're calc is close but you have an error in trying to find the last day of
the month, you had +1 where you wanted -1. Revised calc is:
MosMy = 12*(Year(Dt1)-Year(Dt2)) + Month(Dt1) - Month(Dt2) + |
(DAY(Dt1)/DAY(DATE(Month(Dt1)+1,1,YEAR(Dt1))-1)) - | ! +1))
changed to -1))
(DAY(Dt2)/DAY(DATE(Month(Dt2)+1,1,YEAR(Dt2))-1)) ! +1))
changed to -1))
To keep the result a little cleaner the below code only does the DAY calc if
the DAY() changes. Otherwise 6/1 to 7/1 would generate .998 instead of 1
month. It depends on how the developer wants fractional months handled. One
other way would be to (Day(Dt1)-Day(Dt2))/30.
MosMy2 = 12*(Year(Dt1)-Year(Dt2)) + Month(Dt1) - Month(Dt2) + |
CHOOSE(DAY(Dt1)=DAY(Dt2),0, | If
the Day changed
(DAY(Dt1)/DAY(DATE(Month(Dt1)+1,1,YEAR(Dt1))-1)) - |
(DAY(Dt2)/DAY(DATE(Month(Dt2)+1,1,YEAR(Dt2))-1)) |
)
The below function computes Fractional age and is well tested. The
fractional part is the days since your last Birthday divided by the days
between the last two birthdays (365 or 366)
BDay=DAY(DOB)
BMon=MONTH(DOB)
!Compute Year of the Last Birthday
YOLB=YEAR(TODAY())-CHOOSE(TODAY()
Printed May 3, 2024, 2:41 pm This article has been viewed/printed 35115 times. |