If Then based on a date and number of days

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


Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/11/22 Answer ✓

    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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

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

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/11/22 Answer ✓

    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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Pam Dunn
    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
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

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

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!