Calculating Projected End Date depending on IF Formula

Jack Parry

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

Paul Newcome

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

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

Jack Parry

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

Paul Newcome

Make sure the column you are putting the formula in is set as a date type column.

Jack Parry

@Paul Newcome thanks Paul this worked! Is there a way to make this the number of 30/60/90 Working days?

Paul Newcome

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)

Jack Parry

@Paul Newcome, This worked. Thank you!

Paul Newcome

Happy to help. 👍️