Forumla Help, Nested IFs

@SPark

Hi, Looking to see if someone can help me with this formula. I would like it to follow the following logic:

If Task is checked as complete= "Green"
If Task is past complete by date and not checked as complete= "Red"
If Task is 3 days until complete by date and not checked as complete ="Yellow"

Here is the what I have but it's not working.

=IF([Task Complete]@row, "Green", IF([Complete By]@row > TODAY(-3), "Yellow", IF([Complete By]@row >= TODAY(), "Red")))

Thank you!!

Find more posts tagged with

Formulas

Comments

Mike Wilday

Try this slight modification...

There was no criterion for green. I am assuming its a checkbox based on your statement.

I would put Red next and use the Less than or equal to today. And then I would put yellow last and state Less than today + 3 days. You'll never see the equal two or less than today cause those will always fire Red. Everything else I am leaving blank.

=IF([Task Complete]@row = 1, "Green", IF([Complete By]@row <= TODAY(), "Red", IF([Complete By]@row <Today(3), "Yellow", "")))

Please let me know if this worked for you.

@SPark

Hey Mike,

Unfortunately no, the Green worked when checked but it's doing yellow when its 3 days past the complete by date and all dates leading up to the complete by date is red.

Paul Newcome

Try switching the Red criteria from less than to greater than.

IF([Complete By]@row <= TODAY(), "Red",

switched to

IF([Complete By]@row >= TODAY(), "Red",

Same with the Yellow criteria.

IF([Complete By]@row <Today(3), "Yellow",

switched to

IF([Complete By]@row <Today(3), "Yellow",

Mike Wilday

Hmm. Yes, Paul's suggestion should resolve that. I think I got my greater than, and less than's mixed up. The primary issue then was that the Green didn't have the criterion. Hope that gets it working for you!

@SPark

This is the formula I ended up using at it worked!! Thanks for everyone's help and input!!

=IF([Task Complete]2 = 1, "Green", IF(AND([Complete By]@row > TODAY(), [Complete By]@row < TODAY(+3)), "Yellow", "Red"))

Paul Newcome

Excellent! Glad you found a working solution.

Mike Wilday

I would make your row calls consistent...

IF([Task Complete]@row= 1

=IF([Task Complete]@row = 1, "Green", IF(AND([Complete By]@row > TODAY(), [Complete By]@row < TODAY(+3)), "Yellow", "Red"))

Sign in to join the conversation!

Quick Links

Trending discussions

Netdays Formula
I am wanting to calculate Netdays but only if there is data in 'Corrective Action 1' I am currently using this formula: =IF(ISBLANK([Date Action 1 Complete]@row ), NETDAYS([Date of Event]@row , TODAY()), NETDAYS([Date of Event]@row , [Date Action 1 Complete]@row )) How do I adjust it to only pull a date when Corrective…
Re-write a formula to make it Column Formula compatible
I have Concept phase projects that can result in multiple Phase 1 projects being generated from one Concept Review. I want to calculate the time in the Concept Phase for the first project from that Concept (the Approved Gate 0 UID), but mark it N/A for any subsequent references to that UID. I figured out the formula below…
How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.