Cash Flow Projection
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
-
@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
-
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 (" ").
-
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.
-
Any help would be appreciated
-
@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).
-
That worked! Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!