CODEWITHSUNDEEP
vbams-access
HelloW
HelloW

Reputation: 1617

Does DateDiff Round up?

I found a place in our old code where the original programmer tried to calculate whether an employee had been hired for a certain number of years. The calculation used the difference in days between the date hired and today divided by 364. This didn't make sense to me so I changed it to the difference in years. This also seemed to give an incorrect answer. Does DateDiff round up to the nearest year? Running this formula in the immediate window gives 15 as the answer. I was hoping it would give 14. 

?datediff("yyyy",#3/1/1999#,#2/19/2014#)

Would it be better to use.

?datediff("m",#3/1/1999#,#2/19/2014#)/12

Upvotes: 3

Views: 8549

Answers (3)

Dule Marin
Dule Marin

Reputation: 3

You can do the combination of IIf,DateDiff and DateAdd, like this:

=IIf(DateDiff("m";DateAdd("yyyy";DateDiff("yyyy";[DDOB];Date());[DDOB]);Date())<0;
DateDiff("yyyy";[DDOB];Date())-1;
DateDiff("yyyy";[DDOB];Date()))

So, firstly you calculate months between DDOB plus DateDiff years and Date(), and if integer form DateDiff "m" are in minus, then IIf will reduce the value for the year for one.

Upvotes: 0

SU7
SU7

Reputation: 1766

DateDiff rounds off to the very next year if the year difference is like x years and y months.

For example:

if a person's age is 18 years and 1 months, datediff(yy,DDOB,GetDate()) will give result as '19'.

In case you dont want this rounding off, you can

  • Get difference in days between two dates after casting them in INTEGER
  • Divide the difference with 365.25

  • Use FLOOR to ignore the the decimal part (don't round off as the next number):

    FLOOR((CAST (GetDate() AS INTEGER) - CASR(YourDate AS INTEGER)) / 356.25)

Upvotes: 1

HansUp
HansUp

Reputation: 97131

DateDiff for years only considers the year parts of the dates you supply. And it does not return what you might want as "how many years" ...

For example, the last day of 2013 to the first day of 2014 would be one year as far as DateDiff("yyyy" is concerned.

? DateDiff("yyyy", #2013-12-31#, #2014-1-1#)
 1

Upvotes: 6

Related Questions