Date Calculation Formula

Cheryl Collins
Cheryl Collins ✭✭✭✭✭✭
edited 04/14/21 in Formulas and Functions

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:

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    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 .


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    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.

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Cheryl Collins
    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
    Bassam Khalil ✭✭✭✭✭✭

    @Cheryl Collins

    Ok I will create the formula for you ASAP

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    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 .


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Cheryl Collins
    Cheryl Collins ✭✭✭✭✭✭
    edited 04/15/21

    Hi @Bassam.M Khalil

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

    Thanks

    Cheryl

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Cheryl Collins

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

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!