Formula for dates with a date exception
I have the following date columns
1- last work date:
2- updated availability on:
3- availability expiration date:
I want column 3 to have a date 60 days greater than column 1 unless column 2 has a date from the past 60 days.
so far I have the first part but having trouble with the exception rule.
Thanks for your help!
Best Answer
-
Here's an example sheet I created with that formula in it. Does it look like the outputs are correct?
What the formula does is this:
IF date in Updated Availability On column is within the last 60 days, then the date within Availability Expiration should be 60 days from the date in Updated Availability On. If date in Updated Availablility On column is NOT within the last 60 days, then the date within Availability Expiration should be 60 days from the date in Last Work Date column.
What error did you get when trying to use the formula?
Answers
-
=IFERROR(IF([Updated Availability On]@row >= TODAY(-60), [Updated Availability On]@row + 60, [Last Work Date]@row + 60), [Last Work Date]@row + 60)
-
whoops that didn't work on my end :(
I didn't understand the logic behind the section in bold, shouldn't it reflect the last work date column at that point?
also for the last part, [last work date]@row+60, shouldn't it be todays date? I want the expiration date to be "today" if last work date is more than 60 days ago unless the last update was within past 60 days.
=IFERROR(IF([Updated Availability On]@row >= TODAY(-60), [Updated Availability On]@row + 60, [Last Work Date]@row + 60), [Last Work Date]@row + 60)
-
Here's an example sheet I created with that formula in it. Does it look like the outputs are correct?
What the formula does is this:
IF date in Updated Availability On column is within the last 60 days, then the date within Availability Expiration should be 60 days from the date in Updated Availability On. If date in Updated Availablility On column is NOT within the last 60 days, then the date within Availability Expiration should be 60 days from the date in Last Work Date column.
What error did you get when trying to use the formula?
-
oooh I see. So I just need one slight change for the first part. second part is perfect. see below
IF date in Updated Availability On column is within the last 60 days, then the Availability Expiration should be BLANK as it does not expire
-
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!