calculating number of three month periods between two dates...

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.

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.

--
Regards

Roger Govier


"neil" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

=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
--
AP

"neil" <[email protected]> a écrit dans le message de news:
[email protected]...
> 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.
>

Thanks for the replies.

They work perfectly.

Thanks
Neil.

Hello friend, the formula is perfect but my need is bit different here. There must be a condition wherein if the number of days is 91, it must show two quarters.. i scorched my brains on this..

I dont know, how to start a new thread