TODAY function and status

JenS

I'm working a formula for a RYG column based on two major sets of data:

• Status and Quarter Start
• Status and Quarter End

Q Start and Q End are the dates of the quarter and they are on the sheet as helper columns.

For example:

If Quarter starts in the next 80-120 days:

• blank status - Yellow
• Status is Product discovery, Planning w/ Build Team, or Committed - Green

If Quarter starts in the next 79-0 days:

• Status is Committed or Build - Green
• Status is Product Discovery or planning w/ Build Team - Yellow
• Status is blank - Red

-- Once Quarter is in progress---

• Green: Status is Committed or build and 30 days or more remaining in quarter (Q End)
• Yellow: status is Committed and 30 days or less remaining in quarter
• Yellow: status is build and go live date is blank and quarter ends in 14 days or less
• Red: status is Committed or build and go-live date is blank; quarter ends in 14 or less days

The first thing I did was just try to get something working using the "TODAY" function to understand this function and it is not working (see below). I have no idea how to create the ranges (i.e., if quarter start is in the next 80-120 days - just trying to get started).

IF(AND(Status@row = “Build", [Q Start]@row <= TODAY(30), “Green", IF(AND(Status@row = “Committed", [Q End]@row <= TODAY(30), “Yellow", IF(AND(Status@row = “Build", [Q End]@row <= TODAY(30), “Green", IF(AND(Status@row = “Committed", [Q End]@row <= TODAY(14), “Red", IF(AND(Status@row = “Committed", [Q End]@row <= TODAY(14), “Red”, IF(AND(Release Date@row <> " ", [Q End]@row <= =TODAY(10), “Red“, IF(AND(Release Date@row <> " ", [Q End]@row >= TODAY(), “Red“

Any help is appreciated - even just with getting 1 string of the initial "IF(AND.....TODAY" with the ranges in tact.

Paul Newcome

There are a couple of issues with the above formula that I see right off.

The AND function needs to be closed before moving on to the next portion of the IF statement. Think of the entire AND function as a single item and that is the "logical statement" portion of the IF function.

=IF(AND(this equals this, that equals that), output this, ........................

I also notice it looks like you are using two different types of quotes. See how some are slanted and some are straight up and down? The slanted ones are called "smart quotes" which are (ironically enough) not recognized as valid characters in Smartsheet formulas. Try retyping them directly in the sheet, here in the community, or in a text editor such as Notepad (not Word).

JenS

Thanks! I think I fixed those issues. See below.

IF(AND(Status@row = “Build”, [Q Start]@row <= TODAY(30)), “Green”, IF(AND(Status@row = “Committed”, [Q End]@row <= TODAY(30), “Yellow”, IF(AND(Status@row = “Build”, [Q End]@row <= TODAY(30)), “Green”, IF(AND(Status@row = “Committed”, [Q End]@row <= TODAY(14)), “Red”, IF(AND(Status@row = “Committed”, [Q End]@row <= TODAY(14)), “Red”, IF(AND(Release Date@row <> “ “, [Q End]@row <= =TODAY(10)), “Red”, IF(AND(Release Date@row <> “ ”, [Q End]@row >= TODAY()), “Red”

Can you help me get started on the formula edits to create the ranges? (i.e., if quarter start is in the next 80-120 days)

Paul Newcome

Is the above working for you with the "smart quotes"?

JenS

@Paul Newcome no, the error message is UNPARSEABLE

=IF(AND(Status@row = “Build”, [Q Start]@row <= TODAY(30)), “Green”, IF(AND(Status@row = “Committed”, [Q End]@row <= TODAY(30), “Yellow”, IF(AND(Status@row = “Build”, [Q End]@row <= TODAY(30)), “Green”, IF(AND(Status@row = “Committed”, [Q End]@row <= TODAY(14)), “Red”, IF(AND(Status@row = “Committed”, [Q End]@row <= TODAY(14)), “Red”, IF(AND(Release Date@row <>" ", [Q End]@row <= =TODAY(10)), “Red”, IF(AND(Release Date@row <>" ", [Q End]@row >= TODAY()), “Red”))))))))

Paul Newcome

Ok. So you still need to retype the quotes as mentioned in my last comment.