IF AND with Dates

Options

Help! I've searched and I am stuck! I'm new to SS and I cannot get any formula to work. Here's what I'm trying to do;

If the status is "In Process" and today's date is greater than 7 days from the due date = Green

If the status is "In Process" and today's date is within 7 days of the due date = yellow

If the status is "In Process" and today's date is equal to or after the due date = red

If the status is 'Complete' = blue

Tags:

Best Answers

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

    Hi @JmeC 

    Hope you are fine, please try the following formula and convert it to column format formula:

    =IFERROR(IF(OR([due date]@row = "", status@row = ""), "", IF(AND(status@row = "In Progress",
    [due date]@row >= TODAY(7)), "Green", IF(AND(status@row = "In Progress",
    [due date]@row < TODAY(7), [due date]@row > TODAY()), "Yellow",
    IF(AND(status@row = "In Progress", [due date]@row <= TODAY(7)), "Red")))), "")
    

    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"

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

    @JmeC 

    The following formula will turn it blue if Status = Complete

    =IFERROR(IF(status@row = "Complete", "Blue", IF(OR([due date]@row = "", status@row = ""), "",
    IF(AND(status@row = "In Progress", [due date]@row >= TODAY(7)), "Green",
    IF(AND(status@row = "In Progress", [due date]@row < TODAY(7), [due date]@row > TODAY()),
    "Yellow", IF(AND(status@row = "In Progress", [due date]@row <= TODAY(7)), "Red"))))), "")
    

    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 @JmeC 

    Hope you are fine, please try the following formula and convert it to column format formula:

    =IFERROR(IF(OR([due date]@row = "", status@row = ""), "", IF(AND(status@row = "In Progress",
    [due date]@row >= TODAY(7)), "Green", IF(AND(status@row = "In Progress",
    [due date]@row < TODAY(7), [due date]@row > TODAY()), "Yellow",
    IF(AND(status@row = "In Progress", [due date]@row <= TODAY(7)), "Red")))), "")
    

    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"

  • JmeC
    JmeC ✭✭
    Options

    OMG thank you @Bassam Khalil ! I spent HOURS trying to get it to work and you SAVED me and my sanity!! Sending you a giant virtual hug!!

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/16/21
    Options

    @JmeC

    You are welcome and i will be happy to help you any time.

    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"

  • JmeC
    JmeC ✭✭
    Options

    @Bassam Khalil sorry one more: where do I add the command to turn it blue if Staus = Complete, i tried at the end and it isn't working.

    If the status is 'Complete' = blue

     =IFERROR(IF(OR([due date]@row = "", status@row = ""), "", IF(AND(status@row = "In Progress",
    [due date]@row >= TODAY(7)), "Green", IF(AND(status@row = "In Progress",
    [due date]@row < TODAY(7), [due date]@row > TODAY()), "Yellow",
    IF(AND(status@row = "In Progress", [due date]@row <= TODAY(7)), "Red")))), "")
    

    IF([status]@row = "Complete", "blue"

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

    @JmeC

    I will fix it for you tomorrow because I left my office now.

    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"

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

    @JmeC 

    The following formula will turn it blue if Status = Complete

    =IFERROR(IF(status@row = "Complete", "Blue", IF(OR([due date]@row = "", status@row = ""), "",
    IF(AND(status@row = "In Progress", [due date]@row >= TODAY(7)), "Green",
    IF(AND(status@row = "In Progress", [due date]@row < TODAY(7), [due date]@row > TODAY()),
    "Yellow", IF(AND(status@row = "In Progress", [due date]@row <= TODAY(7)), "Red"))))), "")
    

    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"