Date Range - Start and End Date - Identify dates in a quarter

**Nikki Fox** · 09-11-2012, 09:26 AM

Is there an easy way to calculate this?

If I have date range, e.g. 15 March - 12 June, I want to calculate the following:

(a) how many days (not the difference, but excact days incl. 15th March and 12th June = 90days)
(b) identify how many days fall in each quarter

So for example, if my quarters are Q1 Jan-Mar, Q2 Apr-Jun, Q3 Jul-Sep, Q4 Oct-Dec, from the date range 15 March - 12 June, how many days fall in Q1 and how many fall in Q2.

Is there an easy way to do this.

The reason I ask is I'm calculating energy costs from an electricity bill and need to split the costs equally between Quarters. Unfortunately, the energy company is quoting date ranges that do not fall within my quarter ranges so I need to divide the amount charged by the number of days it covers and then apply a cost to each month/quarter.

Thank you for your help

Nikki

**ShosMeister** · 09-11-2012, 10:07 AM

Guessing if you are getting 87 that you are using the DAYS360 which is based on 30 day months; won't work. If you're just taking the difference of the date, you'll get 89 which is to be expected since the difference isn't inclusive. So just use the difference and add 1 to get the true number of datys between two dates.

**daddylonglegs** · 09-11-2012, 10:41 AM

Hello Nikki,

Try this approach:

Put your start date in A3 (15 March 2012?) and end date in B3 (12 June 2012?) and then in C1 across you can put the quarter names (Q1, Q2 etc.)and in C2 across enter the start date of each quarter, 1-Jan-2012, 1-Apr-2012 etc.

Now in C3 copied across use this formula

=MAX(0,MIN($B3,EOMONTH(C$2,2))-MAX($A3,C$2)+1)

**Nikki Fox** · 09-12-2012, 06:05 AM

Thank you!!!

This works beautifully! I've just got to work out what it means so I can apply this again in the future.

Thanks for all your help.

Nikki