Project days in each quarter

New Contributor

Hi am looking for a solution
I have A1= Start Date, B1= End Date , C1=Q1'20,D1-Q2'20,E1Q3-20,F1-Q4'20, G1= Q1'21,......N1=Q4'22.all up to 12 quarters.
i need a solution- 
the number of project days falling in each of the quarter with my upper limit and lower limit of dates is 1-Jan-20 and 31-Dec-22, A1 and B1 dates could be any days, am only looking how many days in each quarter the project is active between 1-Jan-20 and 31-Dec-22 and they should be in each quarter

5 Replies

@Abhyudoy 

In C4: =MAX(MIN($B4,C$2)-MAX($A4,C$1)+1,0)

Fill to the right to N4.

See the attached version.

You had dates in 2121 and 2222; I have corrected that.

@Hans Vogelaar

Thank you for your quick response, in your solution its returning 93 days between start date and end date, however it should be 92 days...i tried to correct ,,but not finding..:-(..can you help

@Abhyudoy 

Simplified example:

 

Start Date = November 19, 2021

End Date = November 22, 2021

The difference is 22-19 = 3 days, but the time interval contains 4 days: November 19, 20, 21 and 22.

@Hans Vogelaar
Thank you so much for you help!!