Count the number of Saturdays and Sundays in any period in Excel

The following article details how to calculate the number of 7th, Sunday or any day in Excel in many ways.

Example: For the following data table, calculate the number of Saturdays, Sundays, and Mondays.

Count the number of Saturdays and Sundays in any period in Excel Picture 1

There are 2 ways to calculate:

Method 1: Use the Weekday () function.

1. Calculate the number of Sundays

- In cell D5 enter the statement as shown. Similar to cop statements for the remaining cells in column D.

- Note: Make a table of corresponding values ​​of the items in the table below. Sunday value = 1 so when comparing "= 1".

Count the number of Saturdays and Sundays in any period in Excel Picture 2

2. Calculate the number of days 7

- In cell E5 enter the statement as shown. Here day 7 has value = 7 so compare weekday () with value 7 -> Copy the formula for the remaining cells.

Count the number of Saturdays and Sundays in any period in Excel Picture 3

3. Calculate the number of days 2

In cell F5 enter the command as shown. Here the second value of 2 should compare Weekday () with value 2.

Count the number of Saturdays and Sundays in any period in Excel Picture 4

Result:

Count the number of Saturdays and Sundays in any period in Excel Picture 5

You can open the calendar to check.

Method 2: Use the Int () function.

1. Calculate the number of Sundays

- In cell E5 enter the statement as shown. Sunday here is 1 so subtraction 1.

Count the number of Saturdays and Sundays in any period in Excel Picture 6

2. Calculate the number of days 7

- In cell D5 enter the statement as shown. Here Saturday has a value of 7, so subtraction will subtract 7.

Count the number of Saturdays and Sundays in any period in Excel Picture 7

3. Calculate the number of days 2

- In cell F5 enter the command as shown. Monday has a value of 2 so subtract 2.

Count the number of Saturdays and Sundays in any period in Excel Picture 8

=> Results calculated in 2 ways:

Count the number of Saturdays and Sundays in any period in Excel Picture 9

Note: You can calculate the number of days in any order, you just need to change the corresponding value of the item into the formula.

Good luck!