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

enter

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.

excel – How To automatically calculate Qtr Revenue given only two dates and Total Revenue

Leave a Reply

Your email address will not be published. Required fields are marked *