# Thread: [RESOLVED] Excel formula to count holidays from a list excluding weekends

1. ## [RESOLVED] Excel formula to count holidays from a list excluding weekends

I'm trying to write a formula to count public holidays between two given dates (date1 & date2) from a holiday list. Meanwhile, I need to exclude a holiday date if it is a weekend (Saturday or Sunday).

My trial formula: =COUNTIFS(Holiday_Range,">="&Date1,Holiday_Range,"<="&Date2) But that formula counts all listed dates even if it is Saturday or Sunday.

See the attached holiday list. I'm expecting the result is 5 days. But it is 8 days because weekend dates are counted.

I would appreciate any support in that.

2. ## Re: Excel formula to count holidays from a list excluding weekends

One way is via a User Defined Function

Code:
```Public Function countHolidays(fromDate As Date, toDate As Date) As Integer

Dim holidayDateRange As Range
Dim countDays As Integer
Dim holidayDate As Range

Set holidayDateRange = ActiveSheet.Range("HolidayDates")

For Each holidayDate In holidayDateRange
If holidayDate.Offset(0, -1) <> "Sat" And holidayDate.Offset(0, -1) <> "Sun" Then
If CDate(holidayDate.Value) >= fromDate _
And CDate(holidayDate.Value) <= toDate Then
countDays = countDays + 1
End If

End If
Next holidayDate

countHolidays = countDays

End Function```
Or a twostep approach of adding a column that is 0 or 1 if the value in B is Sat/Sun. then summing that column. (EDIT: you can hide the column) Check out this file
https://1drv.ms/x/s!AkG6_LvJpkR7j6k6...-9MpQ?e=yevXUh

3. ## Re: Excel formula to count holidays from a list excluding weekends

Thanks a lot. That was really very helpful.

4. ## Re: [RESOLVED] Excel formula to count holidays from a list excluding weekends

You're welcome