1

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.

0

1 Answer 1

0

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

Your Answer

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