If Then based on a date and number of days

Pam Dunn

I have the following formula that works. When I try to add some IF THEN conditions I can't get formula to work.

=IFERROR(WORKDAY([Due Date]@row, -2, {Holidays Range 3}), "pending")

I am starting with a Due Date I want to calculate a Final File Release Date 2 days prior to Due Date, but if the days until due date column is <3 days I want the formula to bring back the actual Due Date. I also want to ensure the due dates are WORKDAY only and eliminate holidays as well, I don't want a Final due date to fall on the weekend or a holiday.

Here are my attempts neither of these are working.

First formula is to try to get it to bring back the due date if days until due are <3

=IF([Days Until Due Date]@row < 3, WORKDAY([Due Date]@row) {Holidays Range 3})) I get error unparseable

the formula below was an attempt to combine the above scenario with the second part of if the days until due are then subtract 2 days from the due date

=IF([Days Until Due Date]@row < 3, [Due Date]@row, {Holidays Range 3}, IF(OR([Days Until Due Date]@row > 3, [Due Date]@row, -2, {Holidays Range 3}))) I get error incorrect argument set

Jeff Reisman

A few things:

First formula is to try to get it to bring back the due date if days until due are <3

=IF([Days Until Due Date]@row < 3, WORKDAY([Due Date]@row) {Holidays Range 3})) I get error unparseable

You're missing a comma between the WORKDAY([Due Date]@row) and {Holidays Range 3}, and there's an extraneous parentheses, which could cause the unparseable error. But you don't really need to use WORKDAY here.

=IF([Days Until Due Date]@row < 3, [Due Date]@row, {Holidays Range 3}, IF(OR([Days Until Due Date]@row > 3, [Due Date]@row, -2, {Holidays Range 3}))) I get error incorrect argument set

Your use of {Holidays Range 3} would not work in the first part of this formula. The way you have the syntax, your first IF statement is saying if it's less than 3 days until the due date, show me the due date for the row, otherwise here's a list of holidays. 🤔 I think you're overthinking it with this one! Try this:

=IF([Days Until Due Date]@row < 3, [Due Date]@row, WORKDAY([Due Date]@row, -2, {Holidays Range 3}))

The logic: If there are fewer than 3 days until the due date, make this cell equal the Due Date from this row, otherwise (aka more than 3 days until the due date,) make this cell equal two workdays before the due date.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/315811#Comment_315811

Glad I was able to help figure this one out. If you could mark the answer as Accepted, I'd appreciate it!

Jeff Reisman

A few things:

First formula is to try to get it to bring back the due date if days until due are <3

=IF([Days Until Due Date]@row < 3, WORKDAY([Due Date]@row) {Holidays Range 3})) I get error unparseable

You're missing a comma between the WORKDAY([Due Date]@row) and {Holidays Range 3}, and there's an extraneous parentheses, which could cause the unparseable error. But you don't really need to use WORKDAY here.

=IF([Days Until Due Date]@row < 3, [Due Date]@row, {Holidays Range 3}, IF(OR([Days Until Due Date]@row > 3, [Due Date]@row, -2, {Holidays Range 3}))) I get error incorrect argument set

Your use of {Holidays Range 3} would not work in the first part of this formula. The way you have the syntax, your first IF statement is saying if it's less than 3 days until the due date, show me the due date for the row, otherwise here's a list of holidays. 🤔 I think you're overthinking it with this one! Try this:

=IF([Days Until Due Date]@row < 3, [Due Date]@row, WORKDAY([Due Date]@row, -2, {Holidays Range 3}))

The logic: If there are fewer than 3 days until the due date, make this cell equal the Due Date from this row, otherwise (aka more than 3 days until the due date,) make this cell equal two workdays before the due date.

Pam Dunn

@Jeff Reisman Thank you Jeff this worked perfectly. I always miss a comma or yes overthink what I am trying to achieve when composing the formula.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/315811#Comment_315811

Glad I was able to help figure this one out. If you could mark the answer as Accepted, I'd appreciate it!