Need checkbox formula to check if one date is 5 days or more prior to another date

Hello!

I have created checkbox formulas to provide a team's SLA metrics for several key dates. One of the formulas is not working well - the SLA is expected to be 5 days prior to another date. Here is my current formula. The "EB Sent 5 Days Before" date should be 5 days or more before the "Start Date" which will trigger the checkbox. It is checking the box even if the "EB Sent 5 Days Before" field is empty.

=IFERROR(IF(WORKDAY([Start Date]@row, -5) >= [EB Sent 5 Days Before]@row, 1, 0), 0)

I also need to work in holidays. I have found info on adding dates for that formula but where should I place it in the formula?

Thanks so much for your help!

Lorraine

Answers

  • Hi @Lorraine Stevens

    A blank date cell will be seen as "in the past" which is why you're seeing a checkbox. Try adding a simple IF statement at the front of your formula that ignores blank cells, like so:

    =IF([EB Sent 5 Days Before]@row = "", 0, IFERROR(IF(WORKDAY([Start Date]@row, -5) >= [EB Sent 5 Days Before]@row, 1, 0), 0))

    In regards to adding in holiday days, you'll want to do that within the WORKDAY function. I've bolded the section where you would put this:

    =IF([EB Sent 5 Days Before]@row = "", 0, IFERROR(IF(WORKDAY([Start Date]@row, -5, Holidays:Holidays) >= [EB Sent 5 Days Before]@row, 1, 0), 0))


    Or here's another Community thread with discussions around holidays in a separate sheet.

    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Lorraine Stevens
    Lorraine Stevens ✭✭✭✭

    Thank you so much @Genevieve P. ! It worked like a charm. I appreciate your help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!