How do i get a choice column to change from YES to NO depending on another column using calculated columns

Question

I'm trying to uses nested ifs to change what a choice column displays but I'm not sure if this can be done or if I'm doing it properly. What I'm trying to have accrue is once the next review date passes the current date, the reviewed column switches to "No" using the below statement.

=IF(AND([Next Review Date]>=[Date of this Review],Reviewed?="Yes",IF[Current date]>=[Next Review Date],Reviewed?="No"))

I'm relatively new at using calculated columns and I still don't quite understand the syntax.

Columns:

• Reviewed?: Drop-Down Choice Column| "Yes","No"
• Last Reviewed date: Calculated Column| =[Date of this Review]
• Date of this Review: Date and time Column| Default Today's Date
• Next Review date: Calculated Column| =[Date of this Review]+[Review Frequency]

• Review Frequency: Number column

• Status: Calculated Column| =IF(AND([Next Review Date]>=[Date of this Review],Reviewed?="Yes"),"1 - Green","2 - Red")

Answer 1

2 Things:

1: Update your syntax to use the [ ] s consistently as follows ;)

=IF(AND([Next Review Date] >= [Date of this Review],[Reviewed?]="Yes"),"1 - Green", "2 - Red")

2: If you are trying to add this directly into a new calculated column, refer to this answer where I address an issue where adding a new calculated column using an IF function causes an error, but editing an existing calculated column does not.

Answer 2

An alternative solution could perhaps be to use a workflow that changes the value of the choice field based on the next review date and current date?