# Date Calculation Formula

Options
✭✭✭✭✭✭
edited 04/14/21

Hi

I'm after some ideas on the best way to calculate a [Date of Next Review] based on either a [Start Date] or [Last Review Date].

If the Start Date is in the last 12 months of today and [No. of File Reviews] = 0, I need the [Date of Next Review] to be [Start Date] + 3 months

If the Start Date is in the last 12 months of today and [No. of File Reviews] = 1, I need the [Date of Next Review] to be [Start Date] + 6 months

Thereafter the [Date of Next Review] needs to be every 12 months from the [Last Review Date]

I'm assuming I'm going to need to helper columns to achieve this however, I'm really unsure where to start. Below is a screen shot of the columns on my sheet in question.

TIA

Cheryl

Tags:

• ✭✭✭✭✭✭
Options

Hi @Cheryl Collins

Hope you are fine, sorry for the delay because am little bit busy, please find in the following the solution as per your assumptions.

1- the formula for 3 Months from start date =IF(AND([Start Date]@row >= TODAY(-365), [No. of File Reviews]@row = 0), [Start Date]@row + 91, "")

2- the formula for 6 Months from start date =IF(AND([Start Date]@row >= TODAY(-365), [No. of File Reviews]@row = 1), [Start Date]@row + 182, "")

3- the formula for Last Review Date =IF(ISBLANK([6 Months from start date]@row), [3 Months from start date]@row, [6 Months from start date]@row)

4- the formula for Date of Next Review =[Last Review Date]@row + 365

convert all formulas to column format formula and the following screenshot shows the result and you can test it using the following published link for the sheet .

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

Hi @Cheryl Collins

Hope you are fine, i can do those formulae for you but there is something wrong as i see.

If the Start Date is in the last 12 months of today and [No. of File Reviews] = 0, I need the [Date of Next Review] to be [Start Date] + 3 months.

that's means for example if the start date on 15-Apr-2020 and no review done on the file you need the date of the next review to be done on 15-Jul-2020 and this date is already past. so what this will help you.

please confirm is this correct as i understood.

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

Your assumption is correct. If the date of the next review is in the past, this will be highlighted as overdue using conditional formatting.

Thanks so much.

Cheryl

• ✭✭✭✭✭✭
Options

Ok I will create the formula for you ASAP

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

Hi @Cheryl Collins

Hope you are fine, sorry for the delay because am little bit busy, please find in the following the solution as per your assumptions.

1- the formula for 3 Months from start date =IF(AND([Start Date]@row >= TODAY(-365), [No. of File Reviews]@row = 0), [Start Date]@row + 91, "")

2- the formula for 6 Months from start date =IF(AND([Start Date]@row >= TODAY(-365), [No. of File Reviews]@row = 1), [Start Date]@row + 182, "")

3- the formula for Last Review Date =IF(ISBLANK([6 Months from start date]@row), [3 Months from start date]@row, [6 Months from start date]@row)

4- the formula for Date of Next Review =[Last Review Date]@row + 365

convert all formulas to column format formula and the following screenshot shows the result and you can test it using the following published link for the sheet .

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
edited 04/15/21
Options

This is great, thank you so much! You are brilliant. I really appreciate your help. :-)

Thanks

Cheryl

• ✭✭✭✭✭✭
Options

Thank you for your encouraging words and I am at your disposal any time you need any help

bassam.khalil2009@gmail.com

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!