Campaign Spend Flighting by Month

aplunkett02

I am trying to create a formula that can calculate spend by month based on a campaign flight and total budget. Ideally, I would have a column for each month.

I've tried using a helper formula which worked in excel of =MAX(0,MIN($E2,EOMONTH(C$1,0))-MAX($D2,C$1)+1) to get the days in a month and then multiple daily spend by that amount, but Smartsheet doesn't have the EOMONTH function.

(E2 being end date, D2 being start date, and C1 being the start of the month)

KPH

Hi @aplunkett02

The workaround for EOMONTH function is to calculate the date of the last day of the month using the DATE function. Because different months are different lengths, I find the first date of the next month and subtract 1 from it. Would that work for you? If so, I can talk you through the formula.

KPH

Hi @aplunkett02

The workaround for EOMONTH function is to calculate the date of the last day of the month using the DATE function. Because different months are different lengths, I find the first date of the next month and subtract 1 from it. Would that work for you? If so, I can talk you through the formula.

aplunkett02

@KPH

That worked! Thank you!!

KPH

Wonderful! Thanks for letting me know.