How to Fix this Excel Formula?

Question

How to Fix this Excel Formula?

Ryan 0

In the screenshots below you will see the formula I have created as well as the data it corresponds to. The gist of what I am trying to do is have a formula that checks the hire date vs the employees I9 completion date, if the completion date is more than 3 days past the start date it should return "Non Compliant", however I also want it to consider weekends where it will not count them when considering the 3 day deadline. In the data below the hire date was 10/13 and the I9 completion date was 10/21 which is more than 3 days yet it still returns "Compliant".

Formula: User's image

Excel Data:

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Hendrix 2,690 Independent Advisor

Hi Ryan,

Based on your screenshot, the formula is comparing calendar days or using the business-day calculation in the wrong direction. It should compare the I9 completion date with the third working day after the start date.

You can try this formula to see if it works:

=IF(E265="Did not start","N/A",IF(OR([@[Start Date]]="",[@[I9 Completion]]=""),"",IF([@[I9 Completion]]>WORKDAY([@[Start Date]],3),"Non Compliant","Compliant")))

If the formula returns any error, please share a screenshot of the worksheet structure so I can provide a more appropriate formula based on your data.

Hope this helps.

Ryan 0 Reputation points

2026-11-04T21:59:30.6766667+00:00

Hello, This did solve my problem thank you, however it brought up another question. When using the Workday formula how do I use the holiday function especially when holidays are not observed on the same dates every year?
Hendrix 2,690 Reputation points Independent Advisor

2026-11-04T22:14:36.6133333+00:00
Hi Ryan,

Regarding your concern, because some holidays change dates each year, you need to create a separate list containing the actual dates your company observes each holiday.

For example, you place the observed holiday dates in M2:M20. Then, you need to update the formula to:

=IF(E265="Did not start","N/A",IF(OR([@[Start Date]]="",[@[I9 Completion]]=""),"",IF([@[I9 Completion]]>WORKDAY([@[Start Date]],3,M2:M20),"Non Compliant","Compliant")))

WORKDAY excludes dates in the M2:M20 range with Saturdays and Sundays. If a holiday falls on a weekend but your company observes it on Friday or Monday, you need to enter the observed date into this M2:M20 range manually.