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
- 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