Creating a calculated field to show 'Red' 'Amber' or 'Green' based on a due date column

Question

I am trying to create a calculated column to return "Red" "Amber" or "Green" based on a due date.

I have a date column called "Due Date" and I am trying to add a calculated column as follows: If the due date is greater than 7 days, will return "Green". If the due date is between now and 7 days then calculated column value will be "Amber" and if the due date is less than today, "Red".

Can someone please help me out with the formula - I have already inserted the below JSON for conditional formatting and this works but would like the calculated field to work in conjunction.

=if([$field_8]>(@now+604800000), 'Green', if[$field_8]> @now && [$field_8]<(@now+604800000), 'Amber', 'Red'))

thanks in advance.

Answer 1

Try using formula for your calculated column like:

=IF([Due Date] > (TODAY() + 7), "Green", IF(AND([Due Date] >= TODAY(), [Due Date] < (TODAY() + 7)), "Amber", "Red"))

If TODAY() is not working for you, try replacing it with NOW().

Note:

1. Sometimes comma(,) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).
2. Use correct display names of your columns in the formula.

Official Documentations:

1. Calculated Field Formulas