NETWORKDAYS Excluding Partial Holiday

[Becks7x]

Is there a way to use the NETWORKDAYS formula excluding a defined “holiday” that isn’t a full day...

Use case: A custom holiday of three hours.

Thanks!

[Richard Buttrey]

Hi and welcome to the forum,

Not with just the NETWORKDAYS alone.

The approach I'd adopt is to have cells alongside the holiday list of dates which are flagged as a short day in some way. Either with a number of hours or a flag of some sort.

Then let the NETWORKDAYS() function return a value which excludes all the holiday dates, but then extend it by adding a COUNTIFS() function using three criteria. One that checks for a holiday date after or equal to the start dates the second checks for a hoiday date before or equal to the end date and the third that checks for the flag or number of hours.

[Becks7x]

Hi Richard,

Thank you for your reply and welcome note! I am having a hard time envisioning how this would work. I have attached an example workbook with my current formula for NETWORKDAYS (cell H2). You will see this is based on a 8-5 business day. The holidays tab starting row 138 is where I would need to identify a 3 hour holiday on 8/29. Per the use case, my total time should be 3 hours less... See the attached. Thanks!

[Becks7x]

Not sure if the attached file worked... Trying another format.

[Becks7x]

Hi Richard,

Any change you have reviewed the attached spreadsheet? Thanks!

[Richard Buttrey]

Could you offer a few more worked examples with different permutations and set out the rules in a narrative form.
I find the way Excel refers to Table fields and data extremely annoying since it just makes reading formulae very slow. I'd rather work from first principles rather than interpret and adjust what Excel comes up with.

Manually add the results you expect so that I have something to compare with.