Results 1 to 4 of 4

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

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2014
    Posts
    34

    Resolved [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.


    Name:  Holiday List.png
Views: 74
Size:  9.8 KB

  2. #2
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    289

    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
    Attached Images Attached Images  

  3. #3

    Thread Starter
    Member
    Join Date
    Apr 2014
    Posts
    34

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

    Thanks a lot. That was really very helpful.

  4. #4
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    289

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

    You're welcome

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width