Calculating Projected End Date depending on IF Formula
Hi all,
Depending on the type of project being under taken, it will determine the project length, I want use this to estimate an end date for the project by adding 30/60/90 workdays to the start date.
I've used the below formula, buts its returning #UNPARSEABLE, have I missed a parenthesis or a comma? or is the issue that by increasing the days, the months also potentially changes?
=IF([Project Type]@row = "JDI (Up to 1 Month project - Just Do It - run departmentally and CI informed when completed)", DATE(YEAR([SLT Confirmed Start Date]@row), MONTH([SLT Confirmed Start Date]@row), DAY([SLT Confirmed Start Date]@row) + 30))), IF([Project Type]@row = "Kaizen (Up to 3 Month project - Run by department, but doesn't need A3 Thinking and is supported by CI/Green Belts)", DATE(YEAR([SLT Confirmed Start Date]@row), MONTH([SLT Confirmed Start Date]@row), DAY([SLT Confirmed Start Date]@row) + 60))), IF([Project Type]@row = "A3 Thinking (Up to 6 month project and requires CI/Green Belt Coaching)", DATE(YEAR([SLT Confirmed Start Date]@row), MONTH([SLT Confirmed Start Date]@row), DAY([SLT Confirmed Start Date]@row) + 90))
Could I also swap out 'DAY' and replace with 'WORKDAYS' to find out the project end date without counting weekends?
thanks,
Jack
Best Answer
-
You would use a WORKDAY function. The start date would go in the first field and the IF would go in the second field.
=WORKDAY(date, number_of_days)
Answers
-
All you really need to do is take the original date and add the corresponding number of days via the IF statement. You do not need to use the DATE function.
=[SLT Confirmed Start Date]@row + IF([Project Type]@row = "JDI.........", 30, IF([Project Type]@row = "Kazen..........", 60, 90))
-
@Paul Newcome thanks for taking a look at this!
this returns an #InValid Column Value:
=[SLT Confirmed Start Date]@row + IF([Project Type]@row = "JDI (Up to 1 Month project - Just Do It - run departmentally and CI informed when completed)", 30, IF([Project Type]@row = "Kaizen (Up to 3 Month project - Run by department, but doesn't need A3 Thinking and is supported by CI/Green Belts)", 60, 90))
Is there a way of adding the number of WORKDAYs rather than just adding 30/60/90 days?
-
Make sure the column you are putting the formula in is set as a date type column.
-
@Paul Newcome thanks Paul this worked! Is there a way to make this the number of 30/60/90 Working days?
-
You would use a WORKDAY function. The start date would go in the first field and the IF would go in the second field.
=WORKDAY(date, number_of_days)
-
@Paul Newcome, This worked. Thank you!
-
Happy to help. 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.5K Get Help
- 367 Global Discussions
- 202 Industry Talk
- 432 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 447 Show & Tell
- 29 Member Spotlight
- 1 SmartStories
- 285 Events
- 36 Webinars
- 7.3K Forum Archives