Formula for dates with a date exception

Options

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

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @Tzippy

    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

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @Tzippy

    =IFERROR(IF([Updated Availability On]@row >= TODAY(-60), [Updated Availability On]@row + 60, [Last Work Date]@row + 60), [Last Work Date]@row + 60)

  • Tzippy
    Options

    @Mike TV

    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)

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @Tzippy

    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?

  • Tzippy
    Options

    @Mike TV


    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

  • Tzippy
    Options

    @Mike TV

    Hi Mike, would you know how to do that? (keep the cell blank-see previous comment)

    Thanks

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!