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!!

Best Answer

  • Kelly Echo
    Answer ✓

    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), ""))))

Answers

  • Michelle Choate 2
    Michelle Choate 2 Community Champion

    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!

    Michelle Choate

    michelle.choate@outlook.com

    Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate

  • Kelly Echo
    Answer ✓

    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), ""))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!