Calculating date differences into months

[LindaAHogan]

Hi

I have the following calculation in a query:

Months: CInt(DateDiff("m",[TABLE1]![Date1],[TABLE1]![Date2]))

Does anybody know (couldn't find in HELP) how the "m" works

i.e.

27/01/2004 - 07/01/2005 = 11.38 if you divide by 30.4 or
11.44 if divided by 30.25

my query answers 12! (my graph displays 13!)

Thanks

Linda

 

[maxmangion]

your query answer is right. you cannot count both january(s) to get your 13. the datediff simply calculates the difference between the two dates. In fact as far as i am concerned, it does not calculate each month as 30.25, but it considers the actual number of day, that particular month has.

 

[Jon K]

DateDiff("m", [Date1], [Date2]) can't return the correct difference in months. For example, if [Date1] is 31/1/2004 and [Date2] is 1/2/2004, the expression will return 1 month though actually there is only a difference of 1 day.


To return the number of months as an integer, you can use:-
Month: CInt(([Table1].[Date2]-[Table1].[Date1])/30.4)
.