Nested IF with dates

james_c

Hi,

I'm new to smartsheets and looking for some help with the nesting of IF commands so that I can advance a date based on the last date a review was carried out. So IF the "frequency of review" is any of "3 yearly, 2 Yearly, Annually, etc..", then take the "last review date" and add the appropriate number of days.

I can get the formula to work for only one of the if statements but not any additional ones - I get an "unparseable" error. 

This works:

=IF([Frequency of review]5 = "3 Yearly", [Last Review (date format)]5 + 1096)

But this doesn't:

=IF([Frequency of review]5 = "3 Yearly", [Last Review (date format)]5 + 1096), IF([Frequency of review]5 = "2 Yearly", [Last Review (date format)]5 + 731), IF([Frequency of review]5 = "Annually", [Last Review (date format)]5 + 366),IF([Frequency of review]5 = "Bi-Annually", [Last Review (date format)]5 + 183)

Any help greatly appreciated.

Thanks

James

Andrée Starå

Hi James,

Try this.

=IF([Frequency of review]@row = "3 Yearly"; [Last Review (date format)]@row + 1096; IF([Frequency of review]@row = "2 Yearly"; [Last Review (date format)]@row + 731; IF([Frequency of review]@row = "Annually"; [Last Review (date format)]@row + 366; IF([Frequency of review]@row = "Bi-Annually"; [Last Review (date format)]@row + 183))))    

The same version but with the below changes for your and others convenience.  

=IF([Frequency of review]@row = "3 Yearly", [Last Review (date format)]@row + 1096, IF([Frequency of review]@row = "2 Yearly", [Last Review (date format)]@row + 731, IF([Frequency of review]@row = "Annually", [Last Review (date format)]@row + 366, IF([Frequency of review]@row = "Bi-Annually", [Last Review (date format)]@row + 183))))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andrée Starå

James,

I've changed the row number to @ row so you don't have to worry about which row number the formula is at. 

The error in your formula was that you were closing each IF statement with a parenthesis.

Best,

Andrée

Paul Newcome

James,

 

Andree's solution is correct. The reason your formula was returning the unparseable error was because you were closing out each IF statement before beginning the next one.

 

IF statements are built in 3 sections.

1. IF(logical statement is true,

2. then do this,

3. otherwise do this)

.

When nesting IF statements, you start the next one in the third section of the previous one (the otherwise do this section). You then close them all out at the end with a series of closed parenthesis (one for each IF).

james_c

Thanks everyone and now all sorted. A bit of a learning curve to overcome between excel and smartsheet but steadily getting there!

Paul Newcome

Happy to help! 

Andrée Starå

Excellent!

Happy to help!

Best,

Andrée