calculating number of 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.

Also, a quarter (for this purpose) 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 tax 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.

Any suggestions greatly appreciated, as I'm getting quite stumped on
this...


Thanks
Neil.

 

[Ardus Petus]

=INT((DATEDIF(IF(DAY(A1)=1,A1,DATE(YEAR(A1),MONTH(A1)+1,0)+1),IF(DATE(YEAR(B1),MONTH(B1)+1,0)=B1,B1,DATE(YEAR(B1),MONTH(B1-1),0)),"m")+1)/3)

HTH

 

[Roger Govier]

Hi Neil

I think you need to do a couple of intermediate calculations to get
adjusted start and end dates.
I put start date in A2 and end date in B2.
My formula for adjusted start date is in E2
=IF(DAY(A2)=1,A2,DATE(YEAR(A2),MONTH(A2)+1,1))
Formula for adjusted end date is in F2
=IF(DAY(B2)=DAY(DATE(YEAR(B2),MONTH(B2)+1,0)),B2,DATE(YEAR(B2),MONTH(B2),0))
This ensures that we are dealing with complete months starting on day 1
of the month and ending on the appropriate last day of the month.

Then using Datedif the answer for complete quarters is
=INT((DATEDIF(E2,F2,"m")+1)/3)

With your 3 sets of dates I get results of 28, 1 and 1 respectively.

 

[neil]

Thanks for the replies.

They work perfectly.

Thanks
Neil.