1

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")

2 Answers 2

0

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.

0

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?

1
  • Great! Thank You for the good Idea! I'm still fairly new to using workflows so I will need to do more research to figure out how to do that.
    – Mile T.
    Feb 5, 2018 at 16:16

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.