Count Sundays between two dates in excel

How to Count Sundays Between Two Dates in Excel

If you ever need to count how many Sundays fall between two given dates in Excel, a simple VBA function can do the job. Below is a small piece of VBA code that you can use to get the count of Sundays automatically.

VBA Code to count Sundays between two dates

Function CountSundays(startDate As Date, endDate As Date) As Variant
    Dim numSundays As Long
    Dim currentDate As Date

    ' Initialize the count
    numSundays = 0

    ' Check if endDate is before startDate
    If endDate < startDate Then
        CountSundays = "End date can't be earlier than Start date"
        Exit Function
    End If

    ' Loop through each date from start date to end date
    For currentDate = startDate To endDate
        ' Check if the current day is Sunday (weekday number 1 in VBA)
        If Weekday(currentDate) = 1 Then
            numSundays = numSundays + 1
        End If
    Next currentDate

    ' Return the count of Sundays
    CountSundays = numSundays
End Function

How It Works?

1. 1. The function takes two dates as input: startdate and enddate.
   2. It loops through each date in the given range.
   3. It checks if the day is a Sunday (VBA considers Sunday as day 1 in the weekday function).
   4. If a date falls on a Sunday, it adds to the count.
   5. Finally, it returns the total number of Sundays.

How to Use This in Excel

1. Open Excel and press ALT + F11 to open the VBA Editor.
2. Go to Insert → Module and paste the above code.
3. Close the VBA Editor.
4. Now, use the function in a worksheet like this:

   =CountSundays(A1, B1)

   (where A1 has the start date and B1 has the end date).
   Please note, the end date cannot be earlier than the start date. Enter the start date first to begin counting.

Example

If you enter 01/01/2025 in cell A1 and 31/03/2025 in cell B1, the function will return 13, meaning there are four Sundays in that range.

PS: You can create simple add in with this VBA code and then can you this function across your worksheets. With the help of addin this =countSundays() function will work like excel inbuilt formulas. No need to copy and paste VBA in every sheet every time.