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!
You should read it
- How to calculate the number of days in Excel - Functions, formulas for calculating the number of days in Excel
- Instructions on how to count words in cells in Excel
- How to calculate the number of days in Excel
- How to count the number of occurrences of a character in Excel
- Round a number in Excel (ROUND function)
- How to calculate and color blank cells in Excel
- Guide to full Excel 2016 (Part 8): Learn about Number Formats
- How to count characters in Excel
- How to display 0 in front of a number in Excel
- How to number Excel pages from any number
- ABS function (absolute value calculation) in Excel
- Wings calculates the number of days, the difference between two dates in Excel