Date Calculation Formula

Cheryl Collins

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

Bassam Khalil

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 Khalil

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.

Cheryl Collins

Hi @Bassam.M Khalil

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

Bassam Khalil

@Cheryl Collins

Ok I will create the formula for you ASAP

Bassam Khalil

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 .

Cheryl Collins

Hi @Bassam.M Khalil

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

Thanks

Cheryl

Bassam Khalil

@Cheryl Collins

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