Cash Flow Projection

Options

Trying to work a formula that takes the net working days of a date range and spreads across each month within that range.

The formula works until there is a date range that has a year 2024 in it and it still tries to pull the data. I have added some and Year() statements in an attempt to cirvument this calculation but it is reading unparseable. Below is a picture of the sheet along with the original formula as well as my additions.

Original : =(IF(AND(MONTH([Start Date]@row) = 3, MONTH([End Date]@row) = 3), (NETWORKDAYS([Start Date]@row, [End Date]@row)), IF(AND(MONTH([Start Date]@row) = 3, NOT(MONTH([End Date]@row) = 3)), (NETWORKDAYS([Start Date]@row, [Last March 23]#)), IF(AND(NOT(MONTH([Start Date]@row) = 3), MONTH([End Date]@row) = 3), (NETWORKDAYS(DATE(2023, 3, 1), [End Date]@row)), IF(AND(OR(MONTH([Start Date]@row) < 3, YEAR([Start Date]@row) < YEAR(DATE(2023, 1, 1))), OR(MONTH([End Date]@row) > 3, YEAR([End Date]@row) > YEAR(DATE(2023, 12, 31)))), ([Last March 23]# - DATE(2023, 3, 1)) + 1))))) * [$ per day]@row


Adjusted: =(IF(AND(MONTH([Start Date]@row) = 3, MONTH([End Date]@row) = 3, YEAR([Start Date]@row) = 2023, YEAR([End Date]@row) = 2023), (NETWORKDAYS([Start Date]@row, [End Date]@row)), IF(AND(MONTH([Start Date]@row) = 3, NOT(MONTH([End Date]@row) = 3),YEAR([Start Date]@row) = 2023, YEAR([End Date]@row) = 2023)), (NETWORKDAYS([Start Date]@row, [Last March 23]#)), IF(AND(NOT(MONTH([Start Date]@row) = 3), MONTH([End Date]@row) = 3), (NETWORKDAYS(DATE(2023, 3, 1), [End Date]@row)), IF(AND(OR(MONTH([Start Date]@row) < 3, YEAR([Start Date]@row) < YEAR(DATE(2023, 1, 1))), OR(MONTH([End Date]@row) > 3, YEAR([End Date]@row) > YEAR(DATE(2023, 12, 31)))), ([Last March 23]# - DATE(2023, 3, 1)) + 1))))) * [$ per day]@row)



Best Answer

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓
    Options

    @MCODY, give this a try.

    =IF(NETWORKDAYS(IF((DATE(2023,3,1) - [Start Date]@row)<=0, [Start Date]@row, DATE(2023,3,1)), IF((DATE(2023,3,1) - [End Date]@row)>=(-29), [End Date]@row, DATE(2023,3,30)))<1,0,NETWORKDAYS(IF((DATE(2023,3,1) - [Start Date]@row)<=0, [Start Date]@row, DATE(2023,3,1)), IF((DATE(2023,3,1) - [End Date]@row)>=(-29), [End Date]@row, DATE(2023,3,30))))

    The formula will return the number of NETWORKDAYS() for March 2023. Change "3" to the appropriate number for other months. For months with 31 days, change "-29" to "-30". For February: "-27" ("-28" for leap year).

Answers

  • NickStaffordPM
    NickStaffordPM ✭✭✭✭✭
    Options

    So this might be way to simplistic.. but I think you have an extra ) at the end of our formula (bold below):

    =(IF(AND(MONTH([Start Date]@row) = 3, MONTH([End Date]@row) = 3, YEAR([Start Date]@row) = 2023, YEAR([End Date]@row) = 2023), (NETWORKDAYS([Start Date]@row, [End Date]@row)), IF(AND(MONTH([Start Date]@row) = 3, NOT(MONTH([End Date]@row) = 3),YEAR([Start Date]@row) = 2023, YEAR([End Date]@row) = 2023)), (NETWORKDAYS([Start Date]@row, [Last March 23]#)), IF(AND(NOT(MONTH([Start Date]@row) = 3), MONTH([End Date]@row) = 3), (NETWORKDAYS(DATE(2023, 3, 1), [End Date]@row)), IF(AND(OR(MONTH([Start Date]@row) < 3, YEAR([Start Date]@row) < YEAR(DATE(2023, 1, 1))), OR(MONTH([End Date]@row) > 3, YEAR([End Date]@row) > YEAR(DATE(2023, 12, 31)))), ([Last March 23]# - DATE(2023, 3, 1)) + 1))))) * [$ per day]@row)

    This is also the definition of the Unparsabe Error in case this helps problem solve:

    #UNPARSEABLE

    Cause

    The formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.

    Resolution

    Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ").

  • MCODY
    MCODY ✭✭
    Options

    Still gives the same error.

    It seems like there also needs to be another statement that

    If the start month = 3 and the end month does not equal 3, and the start year = 2023 and the end year does not equal 2023 then calculate the networkdays of 3/1/2023 to 3/31/2023.

    Unfortunately, I'm finding that you can not use 2 and statements within an If statement.

  • MCODY
    MCODY ✭✭
    Options

    Any help would be appreciated

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓
    Options

    @MCODY, give this a try.

    =IF(NETWORKDAYS(IF((DATE(2023,3,1) - [Start Date]@row)<=0, [Start Date]@row, DATE(2023,3,1)), IF((DATE(2023,3,1) - [End Date]@row)>=(-29), [End Date]@row, DATE(2023,3,30)))<1,0,NETWORKDAYS(IF((DATE(2023,3,1) - [Start Date]@row)<=0, [Start Date]@row, DATE(2023,3,1)), IF((DATE(2023,3,1) - [End Date]@row)>=(-29), [End Date]@row, DATE(2023,3,30))))

    The formula will return the number of NETWORKDAYS() for March 2023. Change "3" to the appropriate number for other months. For months with 31 days, change "-29" to "-30". For February: "-27" ("-28" for leap year).

  • MCODY
    MCODY ✭✭
    Options

    That worked! Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!