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
-
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!
-
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
-
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 Thank you Jeff this worked perfectly. I always miss a comma or yes overthink what I am trying to achieve when composing the formula.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!