My 'TODAY' formula doesn't seem to be working...?! I've tried =TODAY(), =TODAY(-7) and other permutations but nothing works...?

Hi Kirstine,

What are you trying to do?

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, [email protected])

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

In reply to by Andrée Starå

Hi Andrée, thanks for responding!

I'm working on a really long formula which gives me RYG icons according to what information is in certain cells. In the past I've used =IF([Schedule Approved]2 < TODAY(-14), "Red", (IF([Schedule Approved]2 > TODAY(-7), "Green", "Yellow"))) as part of a longer formula with no problems.

I'm currently working on something which I want to use the same method on but it's not working, and so I've been breaking it down into sections to see what I've missed. What's so annoying is that while this works:

=(IF(AND(Status2 = "Priced", [Quote Submitted]2 < TODAY(-14)), "Red", "Green"))

...this doesn't:

=(IF(AND(Status2 = "Priced", [Quote Submitted]2 > TODAY (-7)), "Green", "Yellow"))

...and it's bugging me so much!!!

Let me know if that's enough info, and if not I'll try to do screenshots etc.

When you are trying to have more than 2 outputs for a IF statement you have to nest those statements.

IF(logical_expression, value_if_true, [value_if_false])

to

IF(logical_expression, value_if_true, IF(logical_expression, value_if_true, [value_if_false])

So it will turn out like this..

=(IF(AND(Status2 = "Priced", [Quote Submitted]2 < TODAY(-14)), "Red", IF(AND(Status2 = "Priced", [Quote Submitted]2 < TODAY (-7)), "Yellow", "Green"))

Let me know if I explained it good enough. :)

In reply to by Michael Kingsbury

This was really helpful, thanks! I'd forgotten about @row etc! Now my finished formula looks like this:

=IF([email protected] = "Inactive", "Gray", IF(AND([email protected] = "Pending Instruction", [Schedule Approved]@row < TODAY(-14)), "Red", IF(AND([email protected] = "Pending Instruction", [Schedule Approved]@row > TODAY(-7)), "Green", IF([email protected] <> "Priced", "Green", IF(AND([email protected] = "Priced", [Quote Submitted]@row < TODAY(-14)), "Red", IF(AND([email protected] = "Priced", [Quote Submitted]@row > TODAY(-7)), "Green", "Yellow"))))))