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

1. ## 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.

2. ## Re: calculating number of three month periods between two dates...

Hi Neil

I think you need to do a couple of intermediate calculations to get
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" <neil@invidion.co.uk> wrote in message
> 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.
>

3. ## Re: calculating number of three month periods between two dates...

=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" <neil@invidion.co.uk> a écrit dans le message de news:
> 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.
>

4. ## Re: calculating number of three month periods between two dates...

Thanks for the replies.

They work perfectly.

Thanks
Neil.

5. ## Re: calculating number of three month periods between two dates...

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..

6. ## Re: calculating number of three month periods between two dates...

Hi

You would be better off starting your own thread this one has now been marked solved so others may not look at it upload a sample worksheet for a better chance of a response.

Chris

7. ## Re: calculating number of three month periods between two dates...

I dont know, how to start a new thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1