formula for past date

Thow Muangsopa

Is it possible to use the same formula but different length of time in the past?

IF(AND([Quote]@row <> "Completed", [Date Sent]@row <= TODAY(5)), "Green",

IF(AND([Quote]@row <> "Completed", [Date Sent]@row <= TODAY(10)), "Yellow", "Red"))))

If today is 04.15.2024 and I need the symbol to change color as days past...

04.15.2024 - 04.20.2024 = Green

04.22.2024 - 04.26.2024 = Yellow

anything after 04.26.2024 = Red

Paul Newcome

You would write it using the syntax that I provided in my above example. A nested IF statement will read from left to right and stop on the first true value.

=IF([Date Quote Sent]@row>= TODAY(-5), "Green", IF([Date Quote Sent]@row>= TODAY(-10), "Yellow", ..........................

Using the above as an example, if it is within the past 5 days, it will be green. If it is not within the past 5 days, it will move on to the second IF statement. Since it already said it isn't within the past 5 days by flagging "false" for the first one, the second one is assumed to be between 5 and 10 days in the past.

Paul Newcome

Happy to help. 👍️

Paul Newcome

That is how that formula is written. Are you getting an error message? Are you able to provide some screenshots of rows where the formula is not working as expected and what you would expect the output to be?

Thow Muangsopa

i get an error with #UNPARSEABLE.

my date column is formatted as a date field.

=IF(AND([Quote Sent]@row <> "Completed", [Date Quote Sent]@row <= TODAY(5)), "Green",IF(AND([Quote Sent ]@row <> "Completed", [Date Quote Sent]@row <= TODAY(10)), "Yellow", "Red"))))

Paul Newcome

Your current formula needs 2 closing parenthesis removed from the very end.

Thow Muangsopa

good call out, that was my fault for not pulling that out (i've shortened the formula when i posted it) but that doesn't solve the issue. the only way that i could solve this issue is to write a formula like this..

=IF([Date Quote Sent]@row = TODAY() - 1, "Green",

IF([Date Quote Sent]@row = TODAY() - 2, "Green",

IF([Date Quote Sent]@row = TODAY() - 3, "Green",

IF([Date Quote Sent]@row = TODAY() - 4, "Green",

IF([Date Quote Sent]@row = TODAY() - 5, "Green",

IF([Date Quote Sent]@row = TODAY() - 6, "Yellow",

IF([Date Quote Sent]@row = TODAY() - 7, "Yellow",

IF([Date Quote Sent]@row = TODAY() - 8, "Yellow",

IF([Date Quote Sent]@row = TODAY() - 9, "Yellow",

IF([Date Quote Sent]@row = TODAY() - 10, "Red",

IF([Date Quote Sent]@row = TODAY(), "Green", "Red")))))))))))

Paul Newcome

Your logic is different between the two formulas. In the first. For example...

[Date Quote Sent]@row <= TODAY(5)

is looking for dates that are less than or equal to 5 days in the future, but in your second formula, the logic is building off of days in the past.

Your newest formula would be re-written as

=IF([Date Quote Sent]@row>= TODAY(-5), "Green", ..........................

Notice the difference in less than or equal to s greater than or equal to as well as looking into the future vs looking into the past?

Thow Muangsopa

yes, I was trying to write a formula that counts the days backward from today. Number of days in the past, change color.

if today is 04.12.2024

04.11.2024 - 04.07.2024 should be green.

04.06.2024 - 04.02.2024 should be yellow.

04.01.2024 and further back should be red and continue to be red.

is it possible to write a formula in range instead of specific days? something like

=IF([Date Quote Sent]@row>= TODAY(-1 through -5), "Green" ?

Paul Newcome

You would write it using the syntax that I provided in my above example. A nested IF statement will read from left to right and stop on the first true value.

=IF([Date Quote Sent]@row>= TODAY(-5), "Green", IF([Date Quote Sent]@row>= TODAY(-10), "Yellow", ..........................

Using the above as an example, if it is within the past 5 days, it will be green. If it is not within the past 5 days, it will move on to the second IF statement. Since it already said it isn't within the past 5 days by flagging "false" for the first one, the second one is assumed to be between 5 and 10 days in the past.

Thow Muangsopa

Thank you, Paul. I made a slight change to accommodate the weekends, but the formula works.

IF([Date Quote Sent]@row >= TODAY(-7), "Green", IF([Date Quote Sent]@row >= TODAY(-14), "Yellow", "Red"))

Paul Newcome

Happy to help. 👍️