Formula needed to find the number of days per quarter when start and end dates are given!

[yemnirmalyem]

When start date and end date are given as 1 Apr 2015/ 31 mar 2016. The excel columns for Q1'15, Q2'15,Q3'15 and Q4'15 should populate the number of days per quarter. Am using financial year( Q1 comprises from Apr -Jun). Dates are varying for three year period from 2013-2016. Kindly help with a formula...

[yemnirmalyem]

using microsoft office 2010

[cbatrody]

Hi,

See the attached file.

[yemnirmalyem]

Thanks Cbatrody. But the Q1 days are 90 as per your sheet. But actual days are 91. April - 30, may - 31 and June -30. Why is it wrong?

[yemnirmalyem]

2016 being a leap year.!!

[yemnirmalyem]

It doesn't work fine when we change the dates as Start Date 1-Feb-2015 to 31-Mar-2015. !!

[cbatrody]

Hi,

I have attached the updated file.

[cbatrody]

It doesn't work fine when we change the dates as Start Date 1-Feb-2015 to 31-Mar-2015. !!

It does! It calculates number of days in a quarter beginning from the "Start Date" Entered.

[yemnirmalyem]

Thanks for your help cbatrody. But changing the start date doesn't work. As i said i have various dates starting from Jan-2013 to March 2016. OPLEASE FIND THE attachment for clarification. Thanks

[cbatrody]

For instances where the start date is not the beginning of the month how do you determine the quarter?

Start Date End Date
13-Jan-14 31-Mar-15
1-Apr-14 10-Apr-14
31-Jan-14 25-Apr-14

[yemnirmalyem]

Quarter is for the months. Like Apr-June 2014 is Q1'14, July-Sep is Q2'14, Oct - Dec is Q3'14 and Jan-Mar 2015 is Q4'14. If it starts at 15 of april 2014. then the quarter should have 15 days less than the normal quarter.

Month is for determinig quarter and date is to define the number of days .

[cbatrody]

Hi,

See the attached file, I have created a table showing the number of days in each quarter for a particular date range.

[yemnirmalyem]

Thanks a lot cbatrody for the formula..!!

[cbatrody]

You are welcome! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.