Formula Help - Exclude Holidays

Elaine Mullis · 11/25/21

Hi, I am trying to edit the formula below, to make the formula not could holidays (It is also set to not count weekends/Today). How would I include it to not count holidays also?

=IF(DATEONLY(TODAY()) = [Requested Due Date]@row, 0, NETWORKDAY(TODAY(), [Requested Due Date]@row))

BullandKhmer · 11/25/21

I'm note sure but I think that holidays listed in the sheet setting are not included in NETWORKDAYS - you will need to test this or have it confirmed by someone else.

If true you could just add holidays in the sheet settings.

Genevieve P. · 11/26/21

Hi @Elaine Mullis

There's an optional piece of the NETWORKDAYS function that allows you to add in Holiday Dates to exclude from the formula, see: NETWORKDAYS function

NETWORKDAYS( start_date, end_date, [ holidays ])

This is in [these] to indicate it's optional.

What I've seen most members do is set up a hidden Date column in their sheet titled Holidays, where they list all the dates that they want to formula to skip. Then you include that range at the end of your formula, like so:

=IF(DATEONLY(TODAY()) = [Requested Due Date]@row, 0, NETWORKDAYS(TODAY(), [Requested Due Date]@row, Holiday:Holiday))

Cheers,

Genevieve

Paul Newcome · 11/26/21

I agree with @Genevieve P..

I have also sometimes created a separate sheet specifically for holiday dates and use a cross sheet reference. This makes it easier if you have to use the same list of holidays across multiple sheets.

Formula Help - Exclude Holidays

Answers

Help Article Resources

Categories