Why DateDiff function resulting "d" and "m" differently

[Ashfaque]

Hi,

It is strange that when I am counting days from-to date, gives me realistic total number of days. But when I am trying to get it in months from same from-to days, it calculating more months.
The Dates are :
LastWorkingDay= 15/03/2020
IqamaExpiryDate =02/09/2020

It is around 171 days. But if you put in VBA Code as below. P resulting 171 days but Q displaying 6......?? and R producing 21.

P = DateDiff("d", LastWorkingDay, IqamaExpiryDate)

Q = DateDiff("m", LastWorkingDay, IqamaExpiryDate)

R = (DateDiff("d", LastWorkingDay, IqamaExpiryDate) Mod 30)

Also I find difference in Excel Sheet Calculation and Access VBA.

Is there someone who can explain or sort this issue? Bcz this making big difference in calculating period.

Thanks in Advance.
Ashfaque

 

[Dreamweaver]

This will explain it:
https://support.microsoft.com/en-us...531-905c-e24fc238f85f?ui=en-us&rs=en-us&ad=us

mod:

Mod Operator - Visual Basic

Learn more about: Mod operator (Visual Basic)

docs.microsoft.com

 

[plog]

A goodway to think of it is that DateDiff operates on the the units in the dates you tell it to work on by immediately throwing away smaller units and not rounding up.

P works on days and there are 171 between your dates. This one probably works as expected.

Q works on months and since they are in the same year its a simple subtraction of 9 - 3. Here's a great one to get your head around it:

DateDiff("m", "29/2/2020", "1/3/2020") = DateDiff("m", "1/2/2020", "31/3/2020")

Those are equivalent because one occurs in February and one occurs in March (3-2). It completely throws out the days.

R is just P but with math based on poor assumptions. This one might be the most important one to learn because it has nothing to do with Access. You cannot convert months into any smaller units (days or weeks) because there's no hard and fast rule about how long they are. Some have 31 days, some have 30 days and then there's February that can't ever decide on just one amount to have. Same with weeks into years (52.14 & 52.28) even days into years (365 & 366).

 

[missinglinq]

Doug Steele and Graham Seach have a 'more complete' Date2Diff Function here that may work for you:

Diff2Dates

Linq ;0)>

 

[Ashfaque]

It looks difficult to get the correct number of days between 2 dates. I copied the lengthy Diff2Date in Access and I tried with Diff2Dates but it works with years "y" only....
X= Diff2Dates ("Y", LastWorkingDay, IqamaExpiryDate) ....this is ok.
but X= Diff2Dates ("ymd", LastWorkingDay, IqamaExpiryDate)... is not working.
X= Diff2Dates ("m", LastWorkingDay, IqamaExpiryDate)... also not working.

It is giving 0 return.

 

[Dreamweaver]

How are you declaring LastWorkingDay and IqamaExpiryDate ??

 

[arnelgp]

Dates in Access follows whatever your Regional date setting in your PC.
to make sure you pass the correct date format, you can use DateSerial() function.

 

[Ashfaque]

How are you declaring LastWorkingDay and IqamaExpiryDate ??

IN DD/MM/YYYY format..