Calculating Due dates based on different criteria

BFuller

Hello,

I have what I think is a complicated issue, but I know you guys can help me.

I have a sheet that creates requests based on a form. On that form, is a question asking if there is a tollgate due date. If they answer in the affirmative, the Actual due date for the project defaults to that date. If there is not tollgate due date, the Actual due date date for the project defaults to our estimated time.

Here are the formulas I use to do this:

Actual Due Date Column: =IF([Tollgate needed]@row = "none", [Projected Due Date Based on All Estimated Time]@row, IF([Tollgate needed]@row <> "None", [Tollgate due date]@row) - 1)

The Actual Due Date is the tollgate due date minus 1 day.

Projected Due Date Based n All Estimated Time column: =IFERROR(WORKDAY([PCD Draft 1 Start Date]@row, VLOOKUP([Combined Doc type and doc status]@row, {Combined and New}, 2, false), {Holidays}), "")

Projected Due date is based on estimated days to complete the various stages of the project.

These two formulas work well, but I have a couple more criteria I need to incorporate. Another question on the form is if there is translation needed. If not, then I want to subtract the estimated translation time from the estimated due date. I need that date to be in Workdays as well. But if there is translation needed, then my current Projected Due date formula will work. I know I need an If statement in there, but I am not sure where to place it.

I also need to give some ability to the assigned person to add days if needed to the due date since we are basing our calculations on estimates. I thought I would give them a column where they can put a number that would then add days to the projected due date column. This way, the Actual Due Date formula would still stand. I need that Tollgate due date to be the default no matter what. So, if the tollgate due date is March 1 and they want to add 15 days to the projected due date and that brings the projected due date to March 4, the actual due date would be February 28 (Tollgate due date minus 1 workday).

I would greatly appreciate help with these formulas! Here is a screenshot of this part of my sheet. You can see I've added an extra days column, but don't know how to amend the current formula for the Projected due date.

Many thanks in advance for your help!

BFuller

I think I figured out my formula. Posting it here to get confirmation:

=IF(AND([Tollgate needed]@row = "None", [Number of languages needed]@row <> "None"), [Projected Due Date Based on All Estimated Time]@row, IF(AND([Tollgate needed]@row = "None", [Number of languages needed]@row = "None"), [Projected Due Date Minus Translation Time]@row, IF([Tollgate needed]@row <> "None", [Tollgate due date]@row) - 1))

David Tutwiler

I think it's close, but I don't see how your second criteria would ever run because it looks identical to your first set of criteria. Did you mean to change either the Tollgate needed to something other than none or change Number of languages needed to something other than none? Otherwise your criteria for option 1 and option 2 match.