How to Calculate How Many Weekends Between Two Dates in Excel

From this tutorial, you will learn how to calculate how many weekends are there between two dates in Excel.

Excel can help us process many different kinds of data and results. One of them is calculating how many weekends we have between two dates. If we want to do this calculation, we need to know the way to write the right formula that can give us the result we want.

Want to know what is that formula we should write to do this kind of calculation? Read the tutorial until its last part!

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more

Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!

How to Calculate How Many Weekends Between Two Dates in Excel

Unfortunately, there isn’t any particular built-in function that can help us count the number of weekend days between two dates in Excel. However, we have the next best thing in Excel, which is a function to count the number of work days between two dates.

Because of the existence of that function (NETWORKDAYS), we just need to calculate the number of days between the two dates we have and then subtract it from the number of work days between them. We will get the result we want after we do that.

Here is the general formula writing of what we have discussed.



And here is its implementation example.



Make sure to input the order of the dates correctly as a wrong order can make you get a wrong number of weekends or even an error.

How to Calculate How Many Sundays Between Two Dates in Excel

Weekend days include Saturday and Sunday. Now, what if we want to calculate the number of Sundays only between two dates that we have in Excel?

For that, we need to ditch our NETWORKDAYS function and replace it with the combination of INT and WEEKDAY in the formula that we write. Here is the formula writing syntax in full.



First, we find the number of days between our two dates by subtracting them. Then, we add that number of days with the result of the WEEKDAY formula that we apply to our earlier date. We do this addition process because we want to know whether we should add an extra Sunday based on the position of our earlier date and its range from the later date.

WEEKDAY will produce a number based on the day of the date that we input into it. By its default process (without a second input), WEEKDAY will produce 1 if our date is Sunday, 2 if our date is Monday, and so on until 7 if our date is Saturday.

To correctly add to the number of days we have from our two dates, we want the maximum number from our WEEKDAY for Sunday (because we want to count the number of Sundays here between our two dates). Thus, we subtract our earlier date with 1 to get 7 if the date is on Sunday.

After we add our number of days and WEEKDAY result, we divide it by 7 (as in 7 days in a week) so we can get a decimal number that represents the number of Sundays we have between our two dates. As we only want a round number for our number of Sundays, we cut the decimal by using INT. Thus, from the formula writing, we get the result that we want from our two-date processing.

To give an example of the formula implementation, let’s implement it on our collection of two dates in the previous example.



As you can see here, the results of our formulas are around half of what we get when we calculate the number of weekends. That is, obviously, because weekend days include Saturdays too besides Sundays.

If we want to count Saturdays instead of Sundays, we just need to adjust the number of days we subtract from the earlier date we input to the WEEKDAY in our formula. As Saturday is already 7 or the highest number we can get from WEEKDAY, we actually don’t have to subtract anything from our earlier date.

Exercise

After we have learned the way to calculate the number of weekends between two dates in Excel, let’s put it into practice.

Open an Excel file and input two dates. Then, try to get the number of weekends and Sundays between them. See a calendar and check whether you get the numbers right from your formulas.

How is it? Can you do it?

Additional Note

Make sure you write your dates in the right format in Excel so Excel recognizes them as dates. Unrecognized dates can make the formulas we have discussed in this tutorial produce errors.

If you are not sure whether you have written your dates the right format, you can just use the DATE function to write your dates. Read this tutorial to learn more about how to use this function properly.



Excel tutorials you may want to read too: