excel – How To automatically calculate Qtr Revenue given only two dates and Total Revenue
excel – How To automatically calculate Qtr Revenue given only two dates and Total Revenue
I made some named ranges to help see what is going on.
A2 = DateA, B2 = DateB, C2 = Revenue
A7 =DATE(YEAR(DateA),1,1) This is to establish the Q1 date for the DateA entered.
A8 =EDATE(A7,3) dragged down to cell A15 as in the picture. This returns the start of the next Quarter.
B7 =IF(AND(DateA>=A7,DateA<A8),A8-DateA,IF(AND(DateB>=A7,DateB<A8),DateB-A7,IF(AND(A7<DateB,A7>DateA),A8-A7))) This is checking how the dates compare to the Quarter start dates and returning the number of days our DateA and DateB date range contain for each Quarter.
C7 =Q&ROUNDUP(MONTH(A7)/3,0) dragged down to read the Qtr Start Date and return the corresponding Q#.
D7 =IF(B7=FALSE,,C7&-&YEAR(A7)) returns the Q# and year when the days column is not false.
E7 =IF(D7=,,(Revenue/(DateB-DateA)*B7/Revenue)) This calculates the percentages of revenue that each quarter contains.
If you change the values of DateA or DateB everything still calculates properly. If you date range is larger than the two years displayed just drag the formulas down to expand the max range.
I hope this helps.