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 (" ").
If you found this comment useful, please let me know by clicking one of the buttons below: Awesome, Insightful, Upvote, or Accepted Answer. Your feedback will assist others looking for the same information and also help me out.
Thanks!
Nick Stafford
-
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
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!