Formula Help - Exclude Holidays

11/25/21
Answered - Pending Review

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))

Answers

  • 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.

  • 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 NewcomePaul Newcome ✭✭✭✭✭

    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.

    thinkspi.com

Sign In or Register to comment.