working out quarters (three-month periods) between two dates

[neil]

Hi,

I've been working on a function to calculate how many quarters there
are between two dates - but not the standard business quarters -
effectively what I am trying to work out is the number of complete
three month periods between two dates.

Example:

Start Date: 27/02/2006
End Date: 19/04/2013

I need to calculate how many complete three month periods have passed
between the two dates - this is not as simple as converting the dates
to a timestamp, defining a year as 60 * 60 * 24 * 365.25 seconds, then
dividing by 4, as the length of months do differ, as henceforth
quarters will differ.

[while the above would work in a lot of cases, it would sometimes fall
due to different lengths of months].

i.e. if you adjust the start dates:

Quarter 1: 1 Feb - 30 April - 89 days
Quarter 2: 1 May - 31 July - 92 days


Therefore, I am trying to figure out how many entire three month
periods has passed between the two dates.

I'm sure this is no specific function that will do, but can it be done
with a combination of other existing function - at the moment it looks
like I'm going to have to write a VBA macro to parse the dates using
lots of IF..ELSE statements.

Thanks
Neil.

 

[neil]

Sorry, I should also have made it clear above that a quarter is defined
as the first day in the period to the last day in the period.

Therefore, 1 Jan - 31 March is a complete quarter (for my purposes),
not 1 Jan - 1 April.

I tried the obvious line:

=FLOOR((DATEDIF(STARTCELL,ENDCELL,"M")/3),1

Using the first set of dates, I get 0, and with the second set I get 1

It is close, but not what I actually am trying to do.

Thanks
Neil.