This post will guide you how to **count the number of weekends between two given dates** in Excel. How to find out how many weekends are between two dates in Excel. How to count the number of Sundays between two dates with a formula in Excel.

Table of Contents

## 1. Calculate the Number of Weekdays between Two Dates

Assuming that you have a list of data in range A1:B4, in which contain start dates and end dates. And you want to calculate the number of weekend days between start date and end date. How to do it. You can use a formula based on the NETWORKDAYS function and the DAYs function to calculate the number of weekends between two given dates.

Here is the formula that it will count the total number of weekend days between two given dates:

**=DAYS(B2,A2)+1-NETWORKDAYS(A2,B2)**

Type this formula into cell C2, and press **Enter** key on your keyboard, and then copy this formula from cell C2 to range C3:C4 to apply this formula.

Let’s see how this formula works:

The NETWORKDAYS function try to calculate the number of working days (weekdays) between two dates, and the DAYS function will count the number of days between two dates. So you can use days number to subtract working days, then you will get the number of weekends days.

## 2. Calculate Number of Weekends between Two Dates Using a User-Defined Function in VBA

let’s dive into the second method, where we’ll create a custom function using VBA.

**Step1:** Launch Excel and access the workbook.

**Step2:**Invoke the Visual Basic for Applications (VBA) editor by pressing **Alt + F11**.

**Step3:**Within the VBA editor, right-click on any item in the Project Explorer, navigate to **Insert**, and select **Module**.

**Step4:**Paste the following VBA code into the module:

```
Function CountWeekends(startDate As Date, endDate As Date) As Integer
Dim currentDate As Date
Dim weekendCount As Integer
currentDate = startDate
weekendCount = 0
Do While currentDate <= endDate
If Weekday(currentDate) = 1 Or Weekday(currentDate) = 7 Then
weekendCount = weekendCount + 1
End If
currentDate = currentDate + 1
Loop
CountWeekends = weekendCount
End Function
```

**Step5:** Exit the VBA editor.

**Step6: **in a cell, enter the formula:

`=CountWeekends(A2, B2)`

Adjust the cell references based on your start and end date cells.

After entering the formula, press **Enter**. The cell will display the count of weekends using your custom function.

## 3. Calculate the Number of Sundays between Two Dates

If you only want to count the number of Sundays between two given dates in Excel, you can use an Excel formula based on the INT function and the WEEKDAY function. Like this:

**=INT((WEEKDAY(B1- 1)-A2+B2)/7)**

Type this formula into cell D2, and press Enter key on your keyboard.

**Note:** if you want to count the number of other week numbers (1-7, 1 is Sunday, 2 is Monday, 3 is Tuesday, 4 is Wednesday, 5 is Thursday, 6 is Friday, 7 is Saturday ) between two dates, such as: Saturday, you just need to replace the number 1 with 7 in the above formula. Like this:

**=INT((WEEKDAY(B1- 7)-A2+B2)/7)**

## 4.Video: Calculate Number of Weekends between Two Dates

This video tutorial will explore how to calculate the number of weekends between two dates in Excel, employing two distinct approaches: a handy formula and a personalized user-defined function (UDF).

## 5. Related Functions

- Excel INT function

The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer.The syntax of the INT function is as below:= INT (number)… - Excel WEEKDAY function

The Excel WEEKDAY function returns a integer value representing the day fo the week for a given Excel date and the value is range from 1 to 7.The syntax of the WEEKDAY function is as below:=WEEKDAY (serial_number,[return_type])… - Excel DAYS function

The Excel DAYS function returns the number of days between two dates.The syntax of the DAYS function is as below:= DAYS (end_date,start_date)…

## Leave a Reply

You must be logged in to post a comment.