# Nested IF with dates

edited 12/09/19

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

• ✭✭✭✭✭✭

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

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﻿

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

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

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

• ✭✭✭✭✭✭

Happy to help!

• ✭✭✭✭✭✭

Excellent!

Happy to help!

Best,

Andrée

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.