Date Diff Calendar Months

[FREDDY67]

My pal has a business which operates on calendar months, but the datediff function in access can't cope or I can't get it to cope, eg.

Date 1 = 28th Dec 2009
Date 2 = 1st Mar 2010

Access datediff returns 2 months 1 day, when in calendar month terms you have 3 days to end of Dec & the whole of Jan + Feb & the 1 day in Mar which
equates to 2 months 4 days.

Any ideas how to resolve this would be appreciated.

Many Thanks

Freddy

 

[namliam]

Well that depends on your way of calculating, ....

28 dec
28 Jan = 1 month
28 Feb = 2 month
1 Mar = 1 day (not including 28 feb)
==> Total 2 months 1 day, which is what datediff does

You want the reverse... AND include the start date, neither of which Datediff does.
Thus you must make your own function/Calculation

 

[FREDDY67]

Namliam

Thanks for the reply, I did know how it calculates the diff but it just doesn't work very well in my opinion eg.

29/12/2009 - 28/2/2010 = 1 month 30 days
29/12/2009 - 1/3/2010 = 2 months 1 day

The actual gap between the dates increases by 1 day but datediff increases the difference by 2 days, I feel the difference should be calculated on the lengths of the months involved in the equation.

Probably just me, but it does seem odd.

Freddy

 

[namliam]

Well you cannot calculate this in one calculation of Datediff (or not that I know off), you have to do it "some other way"

datediff("M", #12/29/2009#, #02/28/2010#) simply returns 2, or it does in A2k
30,31,1,2,3,..., 28 will make 30 seems logical

The problem occurs with interperting 29-dec to what is supposedd to be the non-excisting 29 Feb (or 30 or 31)

It also "increases" by 2 days under the assumption that every month has 31 days, while in reality it changes from 28,29,30 and 31....

 

[FREDDY67]

Namliam

Cheers for the info, I did my own diff in the end, if the date variance is using a greater amount of days in the start month than the end month it uses the start month to calculate that month & actual days in months for the months in-between, & vice versa (I added total days in brackets for an actual ref). Undoubtedly it could be coded better but not by me at this point, i've attached a zip for anyone who wishes to see, as it's easier to see than explain.

Thanks

Freddy

 

[Brianwarnock]

I thought that this would be an interesting exercise but have decided that I don't understand calendar months as if you go from 28th feb 2010 to 28 March 2010 your solution returns 28 days, however from 28 march 2010 to 28 April 2010 returns 1 month and 1 day.

What is a calendar month, which dates are inclusive?

For what its worth I coded up this and it gave 1 month in each case.

Brian

Function ddurbjw(datefr As Date, dateto As Date) As String
Dim nummonths As Long
Dim numdays As Long

nummonths = DateDiff("m", datefr, dateto)
If Day(datefr) = Day(dateto) Then
ddurbjw = nummonths & " Months"
Exit Function
ElseIf Day(datefr) < Day(dateto) Then
ddurbjw = nummonths & " Months " & Day(dateto) - Day(datefr) & " Days"
Exit Function
End If

nummonths = nummonths - 1
numdays = DateDiff("d", datefr, DateSerial(year(datefr), Month(datefr) + 1, 0)) + Day(dateto)
ddurbjw = nummonths & " months " & numdays & " Days"
 
End Function

 

[FREDDY67]

Brian

This is just down to personal preference, the dates all calculate from the first day after the start & include the end date;

ex1: 28th feb 2010 to 28 March 2010

This calculates 28 days in March & as March has 31 days it returns 28 days.


ex2: 28 march 2010 to 28 April 2010

This calculates 3 days in March (29,30,31) & 28 days in April, my logic is that if you are counting 28 days in April & only 3 days in March, April should hold the influence in the calculation. Therefore you have a total of 31 days & as April has 30 days it will return 1 month 1 day.


DateDiff seems to calculate the non-complete month based on starting month, even if only 1 day affects the overall duration.

My logic may be flawed, but I think any calculation should be able to work in both directions. For instance, if you started out with the 29/12/2009 you should be able to add 2 months to it, but as shown in a previous post this would be impossible with DateDiff.

29/12/2009 - 28/2/2010 = 1 month 30 days
29/12/2009 - 1/3/2010 = 2 months 1 day

I hope this clears the muddy waters a bit.

Cheers

Freddy

 

[Brianwarnock]

Datediff calculates the difference in the units requested at a simple level such that 31/12/2009 to 1/1/201 is 1 year, it is also 1 month, and yes 1 day.

That is how it works end of story, and is why there are a number of consumer created age calculations.

Brian

 

[Brianwarnock]

I obviously accept that you will do things the way that you want, however purely out if curiosity I decided to see what Datedif would do.
Datedif is an undocumented, since 97 I think, function in Excel for compatability with Lotus 123. It calculates complete years or months , as well as differences in days ignoring years and months "MD".
The results are below, the 3rd and 4th are interesting.

Brian

	             	            	"M"	"D"	"MD"
28/02/2010	28/03/2010		1	28	0
28/03/2010	28/04/2010		1	31	0
28/12/2009	01/03/2010		2	63	1
28/12/2009	01/04/2010		3	94	4

 

[FREDDY67]

Brian

Like you say it's each to their own, thanks for showing the datediff examples & the difference in 3 & 4 is the bit that bugs me with the function.

The reason it became noticeable is I created a db with command buttons that act like spin buttons to select dates & the duration would alter with each change, but as I held the button the duration would miss certain periods like the 2 month in the previous post.

Anyway thanks for your input & info on how it all works.

Have a good New Year

Freddy