Campaign Spend Flighting by Month

aplunkett02 ✭✭
edited 02/08/24 in Formulas and Functions

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)

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    edited 02/08/24 Answer ✓

    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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!