Note: The other languages of the website are Google-translated. Back to English

How to count the number of days, workdays, weekends between two dates in Excel?

Have you ever needed to count the number of days between two dates in Excel? May be, sometimes, you just only want to calculate the workdays between two dates, and sometime, you need to count the weekend days only between the two dates. How could you count the number of days between two dates in a certain condition?


Count or Calculate the number of days between two dates with formulas

To count how many days there are between two given dates, please apply any one of the following fromulas:

=DATEDIF(A2,B2,"D")
=B2-A2

Then press Enter key, and you will get the number of days between the two dates. See screenshot:

Note: In the above formula, A2 indicates the start date, and B2 indicates the end date. You can replace them as your need.


Count or Calculate the number of workdays between two dates with formulas

But sometimes, you just want to find out the number of workdays (from Monday to Friday), the above formula will not work for you. In this case, here are two functions can help you to deal with this problem.


1. Using NETWORKDAYS function to count the number of workdays

In a blank cell, please enter the below formula:

=NETWORKDAYS(A2,B2)

Then type Enter key, and you will count the number of workdays excluding Sundays and Saturdays between the two dates. See screenshot:

Note: In the above formula, A2 indicates the start date, and B2 indicates the end date.


2. Using NETWORKDAYS function to count the number of workdays but exclude the holidays

Sometimes, there may be some holidays during the two date, if you want to calculate the business days between these two dates, you should exclude Saturdays, Sundays, and holidays. This NETWORKDAYS function also can help you.

The syntax for the NETWORKDAYS function is: = NETWORKDAYS ( Start_date , End_date , Holidays )

Type the holiday dates into the cells that you want to subtract from the dates, then enter this formula into a blank cell where you want to get the result:

=NETWORKDAYS(A2,B2,$C$2:$C$6)

Then press Enter key, the number of workdays which not include Sundays, Saturdays and holidays have been calculated. See screenshot:

Note: In the above formula, A2 indicates start date, B2 stands for end date and C2:C6 is the list of holidays.


3. Using SUM and INT function to count the number of workdays

Except the NETWORKDAYS function, there is another formula could help you to get how many workdays between two dates.

In a blank cell, enter or copy the below formula:

=SUM(INT((WEEKDAY(A2-{2,3,4,5,6})+B2-A2)/7))

And then tap Enter key, and all the workdays will be calculated. See screenshot:

Notes:

1. Sometimes, you have 6 workdays per week (from Monday to Saturday), so you need to calculate the number of workdays including Saturdays but excluding Sundays. In this case, you can apply this formula:

=SUM(INT((WEEKDAY(A2-{2,3,4,5,6,7})+B2-A2)/7))

2. In the above formulas, A2 stands for the start date and B2 indicates the end date.


Count or Calculate the number of weekend days between two dates with formulas

Here also have some formulas to count only the number of weekend days (Saturdays and Sundays) in a given period for you. Please enter or copy any one of the two formulas:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)>5))
=SUM(INT((WEEKDAY(A2-{1,7})+B2-A2)/7))

Then press Enter key, and now you will get how many weekends between these two dates. See screenshot:

Note: In the above formula, A2 indicates start date, B2 stands for end date.


Count or Calculate the number of workdays, weekends between two dates with a useful feature

To count the number of weekdays or weekends between two given dates, Kutools for Excel’s Formula Helper utility which contains some commonly used formulas, you can apply them to calculate ages, word appears and so on.

Note:To apply this Formula Helper, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

1. Click a cell where you want to locate the result, and then click KutoolsFormula Helper > Formula Helper, see screenshot:

2. In the Formulas Helper dialog box, do the following operations:

  • Select Statistical option from the Formula Type drop down;
  • In the Choose a formula list box, select Number of non-working days between two dates or Number of working days between two dates you want to use;
  • Then, in the Arguments input section,select the cells which contain the start date and end date separately.
  • At last, click Ok button to get the result as following screesnhot shown:


Count or Calculate the number of days, weeks, months or years between two dates

With Kutools for Excel's Date & Time Helper feature, you can quickly get the various difference results between two dates based on your need without remembering any formulas, such as weeks + days,months + weeks and so on. Click to download Kutools for Excel!

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!


More relative date and time articles:

  • Calculate Hours Between Times After Midnight In Excel
  • Supposing you have a time table to record your work time, the time in Column A is the start time of today and time in Column B is the end time of the following day. Normally, if you calculate the time difference between the two times by directly minus "=B2-A2", it will not display the correct result as left screenshot shown. How could you calculate the hours between two times after midnight in Excel correctly?

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (47)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Good evening Kindly my inquiry if I have in one cell in excel date and need to extract the number of days, i.e., 13-31/07/2014 is it possible ? thank you Najwa
This comment was minimized by the moderator on the site
Hello, Please be more specific about your issue. To extract all days between 13/07/2014 and 31//07/2014?
This comment was minimized by the moderator on the site
Hello. I am using Method2, but I want to get the - Fridays, Saturdays, and Sundays. However, in the following case, start day: Saturday, end day: Sunday, I get only 1 day instread of 2. Would anyone be able to help in the same? Many thanks,
This comment was minimized by the moderator on the site
Works for me - did you remember to modify the array to refer to the required days? You would need to modify it to {1,6,7} rather than {2,3,4,5,6}
This comment was minimized by the moderator on the site
Using a column list containing the date and time in this format DD/MM/YYYY HH:MM ; what formula(s) would be needed to count how many times each day, and each hour that a cell is entered into this list
This comment was minimized by the moderator on the site
I HAVE FOUR DIFFERENT DATES AND I NEED TO COUNT THE DIFFERENCE BETWEEN THEM WHICH FUNCTION SHOULD I USE AND HOW??
This comment was minimized by the moderator on the site
I would like to get formula which can count 6 work days excluding holidays & certain day is consider half days. Is there any formula that can help? thank you
This comment was minimized by the moderator on the site
I need to calculate the number of days between 2 dates including 1st and last day, and split it into the number of days in each month. E.g. start 28 Jan end 3 march would return Jan = 4, Feb = 28, Mar = 3 how can I do this?
This comment was minimized by the moderator on the site
I need to calculate storage based on days (0-7); days (8-14) and per day days 15 onwards? What formulas do i use?
This comment was minimized by the moderator on the site
I Need to find days in M S excel? can't find by Me give me equation......
This comment was minimized by the moderator on the site
I am trying to calculate the number of days between dates but there are some cells where the 1st date may be blank. I want to return a "0" in that cell when that occurs. I've tried adding an IF formula, but can't get it right. Is there a way I can either ignore the blank cell and not return a value or just have a "0" in the cell?
This comment was minimized by the moderator on the site
I need to calculate storage based on days (0-10); per day days 10 onwards? for exapmle: today's date (03/03/2016) + 10 days = date should come automatically????
This comment was minimized by the moderator on the site
Hello, I need to calculate the difference between two dates for example start on 04/05/2016, and it will end on 20/06/2016, but actually i need one formula calculate to display each month separate " days took in May 24 and on June 20.
This comment was minimized by the moderator on the site
Hi, I need to calculate the downtime to uptime of a machine in minutes during working hours. My working hours are : Monday - Friday 6:00 AM - 7:00 PM Saturday 7:00 AM - 2:00 PM Sundays and Holidays not included For example it's from 10/05/16 (Tuesday) 6:00 PM to 11/05/16 (Wednesday) 8:00 AM, it should only be calculated at 180mins (3 hours) as the downtime will stop on Tuesday 7:00 PM and resume at Wednesday 6:00 AM.
This comment was minimized by the moderator on the site
HI.. How to count today date add to after 60days which date. Ex:27-10-2016 +60days = which Date?
This comment was minimized by the moderator on the site
Hi.. I need your help to get Network days with 2 conditions. Start date ; 01/11/2016 and End date:13/11/2016 Holidays (01/11/2016 and 02/11/2016). 1.Excluded Sundays. 2.Excluded given above Holidays. Thanks, Krishna M
This comment was minimized by the moderator on the site
A B C D E
01-11-2016 13-11-2016 9 01-11-2016 02-11-2016

(SUM(INT((WEEKDAY(A2-{2,3,4,5,6,7})+B2-A2)/7))-((NETWORKDAYS(A2,B2)-(NETWORKDAYS(A2,B2,D2:E2)))))
This comment was minimized by the moderator on the site
Hi, Please provide the formula which include two conditions. If the deference between the two dates are "0 to 5" calendar days than the final result is "Y" and if the deference between the two dates are "greater than 5" or "less than 0" than final result is "N". 15-Jan-2017, 17-Jan-2017 = Y 15-Jan-2017, 21-Jan-2017 = N 15-Jan-2017, 11-Jan-2017 = N 15-Jan-2017, 15-Jan-2017 = Y
This comment was minimized by the moderator on the site
=if((17-Jan-2017-15-Jan-2017)>5,"YES","NO")
This comment was minimized by the moderator on the site
Hi, Please provide a simple formula, i want to add days in a particular date. for example 10.05.2017 and want to add 10 days so answer should come 20.05.2017
This comment was minimized by the moderator on the site
A1= 10-05-2017
B1= A1+10
= 20-05-2017


*note A1 need to be in date format and B1 in General format
This comment was minimized by the moderator on the site
I need to display the days of the week for 28 year cycle, let say from January 1, 1 CE to January 1, 2 CE in Julian calendar. Is it possible to make it automatically? Waiting for answer, sincerely, Hayim
This comment was minimized by the moderator on the site
Alternative formula to avoid weekends is quite useful.
This comment was minimized by the moderator on the site
Hi, I want to find no of days between 2 dates without sundays. Please help me out. Thanks, Bittu Gogoi
This comment was minimized by the moderator on the site
hi
i have a checkin and check out dates, i want to calculate how many Thurs+Fridays are as they have different room rates to the rest of the nights , ie from Sat to Wednesday
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0  Characters
Suggested Locations