How to count all holidays between two dates, excluding those that fall on a weekend - Excel

0
votes

I am able to count the amount of holidays between two dates like this:

=COUNTIFS($T$47:$T$57,">="&F59,$T$47:$T$57,"<="&G59)

The values $T$47:$T$57 are the cells in which my holiday dates are located. F59 is the start date. G59 is the end date.

So basically what this does is count how many times my holidays occur within the start and end date

--e.g.-- start date = 11/24/2016, end date = 11/30/2016 returns 2 (Thanksgiving & Black Friday, which fall on Thursday and Friday).

However, when it comes down to something like Christmas of this year, I am having trouble. Christmas Eve, Christmas Day, and the day after Christmas are counted as holidays but occur on Saturday, Sunday, & Monday, respectively. I don't want to count holidays that fall on a weekend, so my desired return would be 1. However, using my formula, I am currently getting returned 3.

How do I exclude weekends within this problem?

Let me know if anything is unclear. Thanks!

3
excelexcel-formula
is it allowed to ad a column next to $T to represent the weekday of the date as a figure between 1 and 7? – MikeD
Have you considered incorporating the Weekday formula into your table? Using the formula =MOD(WEEKDAY(cell), 7) < 2 will return a TRUE/FALSE statement for you (with True being that the day falls on a weekend.) – Clusks
@MikeD That shouldn't be a problem. However, since my dates are a formula themselves instead of hard-coded dates, they would need to correspond with that. New Year Day is like this '=DATE(T46,1,1)' and Labor day like this '=DATE(T46,9,1)+CHOOSE(WEEKDAY(DATE(T46,9,1)),1,0,6,5,4,3,2)' – Knight
@Clusks I have tried weekday but wasn't sure how to go about it. I needed to return a number value instead of boolean. How would I go about making each day true/false without having a large list of Thursday=TRUE, Friday=TRUE, Saturday=FALSE, etc. That was my dilemma. – Knight
count all dates falling on weekdays SAT/SUN via WEEKDAY() values and subtract this from your gross count ... – MikeD

3 Answers

1
votes

One possibility is to add a weekday number (in example I used =WEEKDAY(date, 2) with SAT/SUN being numbers 6 & 7, respectively (just to please my convention of starting a week on Monday).

Furthermore I used 4 and'ed conditions, the first 2 dealing with start & end date from column B - like you had it, the 2nd two eliminating (from the new range in column C) weekdays with numbers 6 & 7.

enter image description here

Should work for your list as well ...

Other options include to create an array formula or a sumproduct of 1 times a conditional (1 or 0 - depending on weekday)

1
votes

This can easily be accomplished with the NETWORKDAYS or NETWORKDAYS.INTL function by subtracting one with holidays specified from one without.

=NETWORKDAYS(A2, B2)-NETWORKDAYS(A2, B2, $Z$2:$Z$6)

networkdays_intl_elapsed_2

The $Z$2:$Z$6 range of holidays could be made dynamic by creating a named range called holidays with a Refers to: of,

=Sheet1!Z2:INDEX(Sheet1!Z:Z, MATCH(1e99, Sheet1!Z:Z))

Use as,

=NETWORKDAYS(A2, B2)-NETWORKDAYS(A2, B2, holidays)
0
votes

Consider the following UDF()

Public Function KountHolidays(Hlist As Range, fDate As Date, lDate As Date) As Long
    Dim d As Date
    For d = fDate To lDate
        ds = Format(d, "dddd")
        If ds <> "Sunday" And ds <> "Saturday" Then
            For Each r In Hlist
                If r.Value = d Then
                    KountHolidays = KountHolidays + 1
                End If
            Next r
        End If
    Next d
End Function

For completely arbitrary holidays:

enter image description here

The neat thing about this approach is that you can easily adapt this to multiple years.