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.


1 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().


  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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.