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.