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

Lorraine Stevens · 03/10/23

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

Genevieve P. · 03/13/23

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

Lorraine Stevens · 03/13/23

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

Formulas and Functions

Formulas and Functions

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

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Formula - use % complete to determine whether in progress

Formula error

TRYING TO FIND THE ERROR IN THIS FORMULA