Campaign Spend Flighting by Month

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)

Find more posts tagged with

Accepted answers

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.

All comments

That worked! Thank you!!

KPH

Wonderful! Thanks for letting me know.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.