## How to count the number of weekends/weekdays between two dates in Excel?

Sometimes, we need to know how many specific weekdays between two dates. For example, I have two dates: the start date is 1/1/2014 and the end date is 2/15/2014, now I want to know how many times a Sunday or a Monday or a Tuesday etc occurs in this duration. Maybe this is somewhat difficult for us, but here, I can talk about some effective methods for you.

#### Count the number of specific weekdays/weekends between two dates with formula

Supposing, I have the following two dates, and I need to count how many Sundays between them. Select a blank cell, enter below formula, and press the Enter key. And now you will get the number of Sundays between the two dates. See screenshot:

=INT((WEEKDAY(\$C\$2- 1)-\$C\$2+\$C3)/7)

Notes:

(1) In the above formula, C2 is the start date and C3 indicates the end date.

(2) In above formula, 1 stands for Sunday. And you can replace the number 1 with other numbers between 1 and 7. (1 is Sunday, 2 is Monday, 3 is Tuesday, 4 is Wednesday, 5 is Thursday, 6 is Friday and 7 is Saturday)

#### Use formula to count the number of specific weekday in month

Sometimes, you may need to count the total number of a certain weekday in the given month, says count the total number of Wednesday in July, 2020. Here, I will introduce a formula to count the total number of a certain weekday in the specified month easily.

Select a blank cell, type below formula, and press the Enter key to get the counting result.

=INT((WEEKDAY(DATE(G2,G3,1)- G4)-DATE(G2,G3,1)+EOMONTH(DATE(G2,G3,1),0))/7)

Notes:

(1) In above formula, G2 is the specified year, G3 is the specified month, and G4 is the specified day of week.

(2) This formula assigns integers to represent day of weeks: 1 is Sunday, 2 is Monday, 3 is Tuesday, 4 is Wednesday, 5 is Thursday, 6 is Friday, and 7 is Saturday.

#### Use User Defined Function to count the number of specific weekdays/weekends in a given month

You can also create a User Defined Function to calculate how many specific weekdays in a given year and month besides the above formula.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

``````Public Function TotalDays(pYear As Integer, pMonth As Integer, pDay As Integer)
'Update 20140210
Dim xindex As Integer
Dim endDate As Integer
endDate = Day(DateSerial(pYear, pMonth + 1, 0))
For xindex = 1 To endDate
If Weekday(DateSerial(pYear, pMonth, xindex)) = pDay Then
TotalDays = TotalDays + 1
End If
Next
End Function
``````

3. Save this code and return to the worksheet, then in a blank cell, enter this formula = TotalDays(year, month, 1) . In this example, I will count how many Sundays there are in June 2020, so I can apply this formula as one of below formulas, then press Enter key, and you will get how many Sundays at once. see screenshots:

=TotalDays(C2,C3,C4)

=TotalDays(2020,6,1)

Notes: This formulas use integers to represent day of weeks: 1 is Sunday, 2 is Monday, 3 is Tuesday, 4 is Wednesday, 5 is Thursday, 6 is Friday and 7 is Saturday.

#### Count numbers of all weekends/weekdays/specific day of week between two dates with Kutools for Excel

Actually, we can apply Kutools for Excel's Number of non-working days between two dates formula, Number of working days between two dates formula, and Count the number of specific weekday formula to quickly count the numbers of all weekends, weekends, or specific day of week in a date range in Excel.

Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!

1. Select a blank cell you will place the counting result, and click Kutools > Formula Helper > Formula Helper to enable this feature

##### A. Count the number of weekends (Saturday and Sunday) between two dates in Excel

In the Formulas Helper dialog, please do as follows:
(1) Select Statistical from the Formula Type drop-down list;
(2) Click to select Number of non-working days between two dates in the Choose a formula list box;
(3) Enter the start date in the Start Date box (you can also refer a date cell);
(4) Enter the end date in the End Date box (you can also refer a date cell);
(5) Click the OK button.

Now it returns the total number of all Saturdays and Sundays in the selected cell.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

##### B. Count the number of weekdays (excluding Saturday and Sunday) between two dates in Excel

In the Formulas Helper dialog, please do as follows:
(1) Select Statistical from the Formula Type drop-down list;
(2) Click to select Number of working days between two dates in the Choose a formula list box;
(3) Enter the start date in the Start Date box (you can also refer a date cell);
(4) Enter the end date in the End Date box (you can also refer a date cell);
(5) Click the OK button.

And then it returns the total number of weekdays (excluding Saturdays and Sundays) in the selected cell.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

##### C. Count the number of specific day of week (Monday, Saturday, Sunday, etc) between two dates in Excel

In the Formulas Helper dialog, please do as follows:
(1) Select Statistical from the Formula Type drop-down list;
(2) Click to select Count the number of specific weekday in the Choose a formula list box;
(3) Enter the start date in the Start Date box (you can also refer a date cell);
(4) Enter the end date in the End Date box (you can also refer a date cell);
(5) Specify the specific weekday with a integer (1 indicates Sunday, 2-5 means Monday to Friday, and 7 is Saturday.);
(6) Click the OK button.

And then it returns the total number of the specified weekday in the given date range.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

### Best Office Productivity Tools

 🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions… Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ... Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup .... Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List .... Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ... Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ... Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

#### Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Let's say you want to calculate the 3rd Thursday of the month and assume the date of the first of the month is in cell A1. We need to first work out the date of the Thursday in the week of A1. Because Thursday is the 5th day of the week, we use: =A1-WEEKDAY(A1)+5 Then if this Thursday falls before A1, we need to add 7 using [b]((A1-WEEKDAY(A1)+5)
This comment was minimized by the moderator on the site
What about a formula that returns the actual date of the first Wed of each month, Or the 2nd and 3rd tuesday of each month? Or every 3rd tuesday? I want to be able to put in my own start and end dates and then get the actual DATES (not the count) returned to me. Any ideas?
This comment was minimized by the moderator on the site
hi sir, i want to calculate no. of weeks in excel between two dates, but Dose not see right weeks as per date for example: I enterd the date 01/01/2016 ( Friday) and second date 14/01/2016( thursday) no of weeks showing = 2 weeks. but i want to show exact 2 weeks completed 15/01/2016 other wise show previse no fo weeks.
This comment was minimized by the moderator on the site
Let the start and end dates be in cells A1 and A2, respectively. This should work: =INT((A2-A1)/7)
This comment was minimized by the moderator on the site
Is it possible to have the start date set to "=today()" and the end date, for example, the 22nd of the current cycle. As the date returns to the 23rd, refresh the formula to the following 22nd?
This comment was minimized by the moderator on the site
Assuming cycle refers to month, this should work for the end date: DATE(YEAR(TODAY()),MONTH(TODAY())+IF(DAY(TODAY())>22,1,0),22)
This comment was minimized by the moderator on the site
Why are my comments not published completely????
This comment was minimized by the moderator on the site
[quote]Why are my comments not published completely????By Mohamed[/quote] Sorry, please try to send me the formula to jaychivo#extendoffice.com. Please replace @ with #. And i will help you post it. May be there are some characters which have been blocked. :-)
This comment was minimized by the moderator on the site
Hi Jaco, You may achieve this for someone who works Mondays (2), Wednesdays (4) and Fridays (6) as follows: (1) Call the year's start and end dates [quote]StartDate[/quote] and [quote]EndDate[/quote], respectively. (2) List all the public holidays in South Africa (this could span more than one year) in a range and call it [quote]PublicHolidays[/quote] (3) To calculate the total number of days worked enter the following array formula: [quote]=INT((WEEKDAY(StartDate-2)-StartDate+EndDate )/7)+INT((WEEKDAY(StartDate-4)-StartDate+EndDate )/7)+INT((WEEKDAY(StartDate-6)-StartDate+EndDate )/7)-SUM(IF((PublicHolidays>=StartDate)*(PublicHolidays
This comment was minimized by the moderator on the site
Sorry the formula above is not complete: It should be: =INT((WEEKDAY(StartDate-2)-StartDate+EndDate)/7)+INT((WEEKDAY(StartDate-4)-StartDate+EndDate)/7)+INT((WEEKDAY(StartDate-6)-StartDate+EndDate)/7)-SUM(IF((PublicHolidays>=StartDate)*(PublicHolidays
This comment was minimized by the moderator on the site
I don't know what happened to my formula and the rest of my message above. The formula should be: =INT((WEEKDAY(StartDate-2)-StartDate+EndDate )/7)+INT((WEEKDAY(StartDate-4)-StartDate+EndDate)/7)+INT((WEEKDAY(StartDate-6)-StartDate+EndDate )/7)-SUM(IF((PublicHolidays>=StartDate)*(PublicHolidays
This comment was minimized by the moderator on the site
Hi, I am from South Africa and I need advice. I have two workers at work who works different days. Now I want to type in a formula in excel to count how many days a year she work (that I can do), but the trick comes in when I want to type in a formula which allows me to deduct if one of her working days is a public holiday for example she works Monday, Wednesday and Friday. That means she works 156 days per year, but I want excel to deduct the holidays if it is on one of her working days. Can someone please assist me?
This comment was minimized by the moderator on the site
Does not work properly. You need to consider what day you're starting from and ending with!
This comment was minimized by the moderator on the site
Hi Laura, Could you please elaborate?
This comment was minimized by the moderator on the site
Thank you for this. Question, how do I add another day like "Wednesday or 4" to the Monday? Basically I want it to calculate both the total of Mondays and Wednesdays between the two dates. How do I write this formula? Thanks again
This comment was minimized by the moderator on the site
To help future seekers. Use this formula for calculating days between two dates: =NETWORKDAYS.INTL( start_date, end_date, [weekend], [holidays] ) =NETWORKDAYS.INTL(A3,A4,"00000011",C3:C8) - 0=include day 1=exclude day
This comment was minimized by the moderator on the site
THANK YOU VERY MUCH!!! This is perfect! This function exactly does the task!
This comment was minimized by the moderator on the site
Try =INT((WEEKDAY(\$B\$1-2)-\$B\$1+\$B2)/7)+INT((WEEKDAY(\$B\$1-4)-\$B\$1+\$B2)/7)
This comment was minimized by the moderator on the site
Thank You. This is so useful.
There are no comments posted here yet