Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Date Calculation Formula

✭✭✭✭✭✭
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

  • ✭✭✭✭✭✭
    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

    bassam.khalil2009@gmail.com

    ☑️ 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

  • ✭✭✭✭✭✭

    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

    bassam.khalil2009@gmail.com

    ☑️ 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"

  • ✭✭✭✭✭✭

    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

  • ✭✭✭✭✭✭

    @Cheryl Collins

    Ok I will create the formula for you ASAP

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

  • ✭✭✭✭✭✭
    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

    bassam.khalil2009@gmail.com

    ☑️ 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"

  • ✭✭✭✭✭✭
    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

  • ✭✭✭✭✭✭

    @Cheryl Collins

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

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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!

Trending in Formulas and Functions