Forum Discussion
Quarterly revenue formula help needed
lloydantonio No worries, in the newly attached workbook (v2) I used the DATE function to reference the quarterly start and end dates directly within the IF formula, and simplified the logic used in my previous response. I also removed the "Days" column and calculated it directly within the same formula.
Regarding the EOMONTH formula, it can be used to return the last day of the month, n months in the past or future. If you're interested in learning more about this function, please see: https://exceljet.net/functions/eomonth-function
Surely this is not your usual style! 
This is taken directly from the djclements formula but refactored to give the result as a dynamic array.
=LET(
qtr, {1, 2, 3, 4},
closeDate, IF(
DATE(calendarYear, 1 + 3 * qtr, 0) < End,
DATE(calendarYear, 1 + 3 * qtr, 0),
End
),
openDate, IF(
DATE(calendarYear, 1 + 3 * (qtr - 1), 1) > Start,
DATE(calendarYear, 1 + 3 * (qtr - 1), 1),
Start
),
partQtr, (closeDate - openDate + 1) / (End - Start + 1),
percent, MAP(partQtr, LAMBDA(x, MAX(x, 0))),
revenue, percent * totalRevenue,
HSTACK(percent, revenue)
)There are slight differences in the results, but I think that is due to the difference in calculating month ends (otherwise it may be me fouling up).