Cash Flow Projection

MCODY

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)

Toufong Vang

@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).

NickStaffordPM

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

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

Any help would be appreciated

Toufong Vang

@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

That worked! Thanks!