Number of months between 2 dates

[Bisquite]

Anyone ever written a formula to calculate the number of months (including part months) between 2 dates? I have been struggling to come up with something that works for all cases.

Example 1:
Start Date = 01-Jan-04
End Date = 30-Jun-04
Months = 6

Example 2:
Start Date = 01-Jan-04
End Date = 15-Jun-04
Months = 5.5

Example 3:
Start Date = 20-Jan-04
End Date = 05-Jun-04
Months = 4.5333

The main problem I have is deciding whether to calculate the part months based on a standard 30 day month or to calculate them based on the month they fall in.
For example:
Start Date = 20-Jan-04
End Date = 31-Jan-04
Is this
a) 11 / 30 = 0.3666 months, or
b) 11 / 31 = 0.3548 months ??

If anybody has ever done anything like this before I would be interested in hearing about it.

Thanks

 

[synapsevampire]

Try something like the following to base it on the day of the month:

if day({table.date2}) < day({table.date1}) then
datediff("M",{table.,date1},{table.date2})-1
else
datediff("M",{table.,date1},{table.date2})

I can't test right now, but this should be close.

-k

 

[davidwendelken]

Your underlying database may have a function to do just that. You can use it in your query.

For example, Oracle has a MONTHS_BETWEEN function.

David Wendelken

http://www.casetech.net

 

[Bisquite]

Thanks Synapse but that would only round to the nearest whole month, I am also interested in part months, hence the .5 in example 2.
David, we use SQL Server but I don't want to start writing stored procedures or views to write reports on, I would rather keep it just to Crystal.

The current formula we use is below, it's reasonably accurate but falls over when you start involving February:-

local numbervar months := datediff("m",startdate,enddate);
local datevar beginlast := date(dateadd("m",1,startdate)-1);
local datevar endlast := date(dateadd("m",1,enddate)-1);
local numbervar begpart := day(startdate)/(beginlast - startdate + 1);
local numbervar endpart := day(enddate)/(endlast - enddate );

result := months - begpart + endpart

 

[Madawc]

synapsevampire's formula would give you the number of calendar months, which is the normal usage. If you want the number of 30-day periods, you can adapt the formula to show number of days, something like

datediff("d",{startdate},{enddate})

Display the number of days next to the data, just to be sure it is working. Then get the number of whole 30-day periods. If the day-number formula was @DaysDiff, you could get this as

Truncate(@DaysDiff/30)

You get better results from Crystal by using formual fields, running totals etc. wherevre possible. Use variables only when it's absolutely necessary; page totals or shared variable to pass back data from subreports.

Madawc Williams (East Anglia, UK)

 

[Bisquite]

Thanks Madawc but this wouldn't work well enough as it's too big an assumption. If the start and end dates were 01-Feb-05 and 28-Feb-05 then this formula would give you 0.93 months but you want 1.0 months. I know you could put lots of conditional if statements to check the month but there must be a neater solution.

 

[Madawc]

Solution to what? Do you regard it as a whole month if the start and end include the last and first day? If so, try datediff("m",{startdate}, ({enddate}+1))

Madawc Williams (East Anglia, UK)

 

[Bisquite]

But that wouldn't work if the start and end dates didn't fall at the start and end of the month.

 

[Madawc]

So what is the rule?

Madawc Williams (East Anglia, UK)

 

[smcnulty2000]

This is actually a painful little problem.

If I understand correctly, you want all the months between two dates to be counted as full units, and you want any partial months between those two dates to be treated as fractions of the months they live in.

So if either date is sometime in January, you want to divide by 31 for the days in that month only, and if either date is in February, you want to divide by 28 or 29 based on whether there is a leap day, again for the days that occur in that month only. And so on.

My inclination would be to suggest this:

=================
evaluateafter({@Startdate});
local datevar startdate:={@Startdate};
local datevar enddate:={@EndDate};
local numbervar result;

local numbervar months := datediff("m",startdate,enddate)-1;

//Process the start date
local datetimevar startdtNextMonth := dateadd("m",1,startdate); //Get the following month
local numbervar startdtyear := year(startdtNextMonth); //Get that month's year
local datetimevar StartDtEOM := dateadd("s", -1, date(startdtyear, month(startdtNextMonth), 1)); //Get the first day of that month, then reduce by one second.

//Process the end date, as the previous portion.
local datetimevar enddtNextMonth := dateadd("m",1,enddate);
local numbervar enddtyear := year(enddtNextMonth);
local datetimevar EndDtEOM := dateadd("s", -1, date(enddtyear, month(enddtNextMonth), 1));

//Collect the spare parts of the end and beginning months, fractions of month based on their length of month.
local numbervar startdaysleft := (StartDtEom-startdate)/day(StartDtEOM); //start date to end of that month.
local numbervar enddaysleft := day(enddate)/day(EndDtEOM); //end date, from beginning of that month.

result := months+startdaysleft+enddaysleft;

=================

Which is probably a little closer to the mark for what you are trying to do.

This method is based on finding the end of the month down to the second, which gives a different result than if you find the end of the month down to the day. Either of those probably make sense given different circumstances.

Obviously you can clean up this code to fit your own naming conventions and perhaps improve it in other ways.

Scott M.

 

[Bisquite]

Thanks Scott, I will give this a go. I am still undecided as to whether partial months should be treated as fractions of the month they live in or fractions of a standard 30 day month. Any thoughts?

 

[smcnulty2000]

Well, with some consideration, the 30-day month is one of those things you want to use judiciously rather than as a universal solvent.

Naturally, if your data were prone to using 30-day months I'd be all for it.

Were it me and my data I'd only use the 30-day model if the client insisted or the data seemed to somehow suggest it. Otherwise I'd have to keep adjusting the dates or the results or risk watching them wander off.

What kind of data do you work with? That's never come up, but it tells a lot about what problems you are dealing with.

Genealogy, finance, real estate, statistics? All of these have used 30-day intervals to 'smooth out' the months. Which I think negates the fact that the data was usually designed around our gregorian months when it was assembled, collected or assumptions made. I think the 30-day model is counter-processing of data in many cases.

Scott M.