IF AND formula with between dates

I'm trying to toggle and RYGG status field with a formula. The idea

If the contract send day is more then 14 days ago and the contract hasn't been executed I want the status to be RED. If the contract day is more than 7 days, less than 13, and the contract hasn't been executed the status should be YELLOW. Otherwise it should be Green.

Here is the formula I am working with, but it keeps telling me this is unparseable. Can anyone tell me what I'm missing?

=IF(AND([Send Date]1 - TODAY() < 14, [Contract Executed]1 = "false"), "Red", IF(AND([Send Date]1 - TODAY() < 7, [Send Date]1 - TODAY() > 13, [Contract Executed]1 = "false"), “Yellow”, ”Green"))

Find more posts tagged with

Formulas

functionality

Accepted answers

Xander Brennan

I was able to figure it out.

I had the date calculation backwards needs to be Today() - [Send Date] to get a positive number for the greater-than formula to work.

All comments

Xander Brennan

I was able to fix it by fixing the quotes, but not matter what I get RED unless the Contract Executed is checked.

Andrée Starå

Hi Xander,

What's your column names?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Xander Brennan

I was able to figure it out.

I had the date calculation backwards needs to be Today() - [Send Date] to get a positive number for the greater-than formula to work.

Andrée Starå

@Xander Brennan, Glad you got it working!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.