Formula for dates with a date exception

Tzippy

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!

Find more posts tagged with

Accepted answers

Mike TV

@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?

All comments

Mike TV

@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

@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

@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:

What error did you get when trying to use the formula?

Tzippy

@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

@Mike TV

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

Thanks

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.