Formula for "days since" creation date, if a box is not checked

Options

Hi there, I'm trying to write a formula for an "at risk" type of column based on the following criteria:

  1. If it has been five work days since the row creation date (based on a system generated "created date" column")
  2. If a "supervisor approval" box is NOT checked

If these two criteria are met, I want the cell to show 1, if both the criteria are not met, I want the cell to show 0.

Here is a screenshot


Thank you in advance!

Best Answer

  • Sing C
    Sing C ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Lindsay Scarey,

    Try this in an At Risk column (this is based on it being a checkbox column):

    =IF(AND([Supervisor Approval]@row = 0, NETWORKDAYS(Created@row, TODAY()) > 5), 1, 0)
    

    The NETWORKDAYS formula calculates the number of working days between two dates. It is then compared to 5 to set the checkbox accordingly. Depending on your needs (i.e. if the check is 5 days or more than 5 days), you can change it to comparison to TODAY()) >= 5

    I hope that helps. Let me know if that does the job for you.

    Have a great day!

    Sing Chen

    Process Architect, Ceridian

    LinkedIn

Answers

  • Sing C
    Sing C ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Lindsay Scarey,

    Try this in an At Risk column (this is based on it being a checkbox column):

    =IF(AND([Supervisor Approval]@row = 0, NETWORKDAYS(Created@row, TODAY()) > 5), 1, 0)
    

    The NETWORKDAYS formula calculates the number of working days between two dates. It is then compared to 5 to set the checkbox accordingly. Depending on your needs (i.e. if the check is 5 days or more than 5 days), you can change it to comparison to TODAY()) >= 5

    I hope that helps. Let me know if that does the job for you.

    Have a great day!

    Sing Chen

    Process Architect, Ceridian

    LinkedIn

  • Lindsay Scarey
    Lindsay Scarey ✭✭✭
    edited 12/06/23
    Options

    @Sing C Thank you for taking the time to answer! I got my at risk column going! Yay!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!