This post demonstrates how to calculate number of weekdays in a given date values in excel.
Suppose we have a column in excel that has date values and we want to count the number of weekdays in it.
We could write a simple excel formula using WEEKDAY function in excel to determine the day number and could count the number of weekdays in a date column in excel.
As you could see below, the number of weekdays (Monday to Friday) in the column A is 8.
Step 1
Enter formula =WEEKDAY(A2,2) in cell C1 to find out the day number of a date.
The second argument in the WEEKDAY formula determines the numbering system of the formula, I have entered it as 2 which will start with Monday(1) to Sunday(7).
Had I entered the second argument as 1, the numbering would have started from Sunday(1) to Saturday(7).
And press enter and drag the formula till you have data in column A.
Step 2
As a next step you could see that we have numbers assigned to each date e.g. Wed is assigned 3(as it is third day from Monday, our second argument in the formula i.e. 2, will start numbering from Monday)
Step 3
So now we could understand that any number greater than 5 would be weekends (6 for Sat and 7 for Sun).
Enter the formula =COUNTIF($C$2:$C$12,”<=”&5) in cell D2.
Here we are counting the numbers in the range that are less than or equal to 5, in other words looking for Weekdays.
Hit enter, and you could see our formula has calculated the number of weekdays in excel.
You could do a little bit of modifications in the formula to count the number of weekends in a date column in excel, just replace the “<=5” sign in the countif formula with “>5”, which will look for weekends.
Hope this helped
