trouble to get number of months given two dates

  • Thread starter Thread starter Associates
  • Start date Start date
A

Associates

Hi,

I was wondering if anyone might be able to help me here.

I was trying to get the following code to work but to no avail.

periodofEmployment = DateDiff("m", DateJoined, CurrDate) + DateDiff("md",
DateJoined, CurrDate) / 29 ' returns number of Months

What the code is supposed to work is to work out the different between the
joined date and the current date and returns a number of months. The code
"DateDiff("md", DateJoined, CurrDate) / 29" would give a more accurate
figure. for example, if the joined date falls on 10-Jan-09 and the current
date is 06-Oct-09, it would then return 8.9 months

Apparently, VBA does not like "md" in the second DateDiff. I tried to use
just the "d" but this returns incorrect value.

Any help would be greatly appreciated.

Thank you in advance
 
You have a couple problems. First problem is
DateDiff("m", DateJoined, CurrDate)
will return the number of month transitions. So Jan 31 to Feb 2 will return 1
for the number of months.

You might try the "More Complete DateDiff Function" Graham Seach and Doug
Steele wrote. This could be used to return the number of months and days.
You would have to parse the string to get the number of days to divide by 29.

http://www.accessmvp.com/djsteele/Diff2Dates.html

You specify how you want the difference between two date/times to be
calculated by providing which of ymdhns (for years, months, days, hours,
minutes and seconds) you want calculated.

For example:

?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

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top