Complicated Forecast - Date + Network Days + Margins

I am trying to create a detail financial forecast based on the number of working days * daily margin for each month in a relevant date period.

I have a look up table with all months and the working days minus UK holidays and I am as far as calculating if the months in the date period apepar, HOWEVR I cannot figure out a way to calculate/include formula for PARTIAL month start/ends, OR if I add a date extension to calculate a longer period.

Current formula (taken from January as example)

=IF(AND([Start Date]@row < DATE(2025, 2, 1) - 1, [End Date]@row >= DATE(2025, 1, 1)), Margin@row * VLOOKUP("Jan 2025", {Working Days Range 1}, 2, false), "")

HELP!!

Find more posts tagged with

Accepted answers

Kelly Echo

Thank you Michelle - that was the name of my range (easier to keep it that way) but appreciate the reminder of Index/Match. I have now cracked this formula and got it working!

This is what I ended up with:

=IF(CONTAINS("Jan 2025", [Placement Start]@row), Margin@row * INDEX({Working Days Data}, MATCH("Jan 2025", {Working Days Year}, 0), 2) - ([Start Adjustment]@row * Margin@row), IF(CONTAINS("Jan 2025", [Placement End]@row), Margin@row * INDEX({Working Days Data}, MATCH("Jan 2025", {Working Days Year}, 0), 2) - ([End Adjustment]@row * Margin@row), IF([Extended?]@row = true, IF(AND([Start Date]@row < DATE(2025, 2, 1), [New End Date]@row >= DATE(2025, 1, 1)), Margin@row * INDEX({Working Days Data}, MATCH("Jan 2025", {Working Days Year}, 0), 2), ""), IF(AND([Start Date]@row < DATE(2025, 2, 1), [End Date]@row >= DATE(2025, 1, 1)), Margin@row * INDEX({Working Days Data}, MATCH("Jan 2025", {Working Days Year}, 0), 2), ""))))

All comments

Michelle Choate 2

Hello @Kelly Echo ! I would love to start off with some best practices to start.

1.) I always recommend using INDEX/MATCH rather than VLOOKUP for a few reasons - the most important of which is the ability to add as many columns as you want in the source sheet without breaking your formulas!

2.) The second best practice I would like to tell you about is naming your ranges. So rather than using "Working Days Range 1" it would be to name the column you are aiming for so as "Working Days | End Date" or "Working Days | Entire Sheet" if it is all the columns, or something else that is very specific.

When I am naming my ranges I always use this formula "Sheet Name | Column Name" or if I am referencing a row, the row name instead of Column name. The reason I always use the Sheet Name in my references is because I often use multiple sheets with the same column name, so it is best to know EXACTLY where a reference is from. Also, if I have to trouble shoot a formula months to years in the future, I know exactly what it was SUPPOSED to be.

On to your actual question! There are several ways to include partial months, the easiest is usually an IF statement. For instance =IF(DAY([Start Date]@row)>1, then whatever the formula would be to calculate that partial month. If you want to run through what you are exactly looking for when it comes to partial months, and can share the names of the columns on your lookup table, I could help you here. Alternatively, we could do a quick walkthru on zoom. My calendar link is in my signature. Feel free to throw some time on my calendar and we can can do just that any time that works for you!

I just created a really neat formula that looks at the start date of any project plan, and is able to then say what MONTH 1 is, and rolls everything all the way through! Super fun formulas!

Kelly Echo

Thank you Michelle - that was the name of my range (easier to keep it that way) but appreciate the reminder of Index/Match. I have now cracked this formula and got it working!

This is what I ended up with:

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.