IF - Date Range

Options
A Rose
A Rose ✭✭✭✭✭
edited 10/25/21 in Formulas and Functions

Hi,

Can you help me with a formula for the below argument?

IF Created@row is more than 28 days In the past, "4 Weeks Overdue", IF Created@row is more than 35 days In the past, "5 Weeks Overdue"

Thank you!

Best Answer

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

    Hi @A Rose

    Hope you are fine, please try the following formula :

    =IF(AND(Created@row <= TODAY(-28), Created@row > TODAY(-35)), "4 Weeks Overdue",
     IF(Created@row <= TODAY(-35), "5 Weeks Overdue", ""))
    

    the following screenshot shows the result:


    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

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

    Hi @A Rose

    Hope you are fine, please try the following formula :

    =IF(AND(Created@row <= TODAY(-28), Created@row > TODAY(-35)), "4 Weeks Overdue",
     IF(Created@row <= TODAY(-35), "5 Weeks Overdue", ""))
    

    the following screenshot shows the result:


    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"

  • A Rose
    A Rose ✭✭✭✭✭
    Options

    Hi @Bassam Khalil ,

    That was a big help!

    Can we go a little further?

    we now have 4 weeks and 5 weeks overdue,

    I want to add when "6 Weeks Overdue" and when "More than 6 weeks overdue"

    Is that possible?

    Thank you!

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @A Rose

    Please try the following formula :

    =IF(AND(Created@row <= TODAY(-28), Created@row > TODAY(-35)), "4 Weeks Overdue",
     IF(AND(Created@row <= TODAY(-35), Created@row > TODAY(-42)), "5 Weeks Overdue",
     IF(AND(Created@row <= TODAY(-42), Created@row > TODAY(-48)), "6 Weeks Overdue",
     IF(Created@row <= TODAY(-48), "More than 6 weeks overdue"))))
    

    the following screenshot shows the result:


    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"

  • A Rose
    A Rose ✭✭✭✭✭
    Options

    Hi @Barry Bowles ,

    I tried the below, would this give me different results?

    (Used little different term)

    Thank you!

    =IF(Created@row <= TODAY(-49), "Past 6 Weeks", 
    IF(Created@row <= TODAY(-42), "6 Weeks", 
    IF(Created@row <= TODAY(-35), "5 Weeks", 
    IF(Created@row <= TODAY(-28), "4 Weeks"))))
    

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!