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!
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!
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.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
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!
Not sure if the attached file worked... Trying another format.
Hi Richard,
Any change you have reviewed the attached spreadsheet? Thanks!
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks