Welcome!

Nested IF with dates

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

Find more posts tagged with

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

I am getting an error message when attempting to export to excel
I am attempted to export to excel. I have two different smartsheet documents to export. One of them exported to excel fine, but not the other. This happened a few weeks ago. I make sure all columns were the correct size & no cells were hidden. "We have found a problem with (file) Do you want us to try and recover as much…
Need Help w/ Report View - Nothing Is Showing
Hello Smartsheet Community, I need some help with a report that I created and shared with a customer. The customer is not seeing any of the information that is pulling into the report. See screen shot below that the customer texted me. I do have the report filter only showing the customer their product pulling from our…
How can I hard code a cell based on a trigger?
BLUF: When I create an Invoice, I need the cell-link to my live price data to be severed. I currently have a Price sheet for my products that is updated frequently. I use Control Center to create Projects, which includes an Invoice sheet. I have a dropdown on my invoice sheet to select a product, and then I use Index…