Count Specific Weekday or Weekdays within Data range containing Dates

Often we have to deal with dates as they form integral part of almost every data set. But what if you want to count specific weekdays within a large range of dates? This is often the case with time sheets or similar kind of data set where dates are involved and you have to count specific days like Saturdays, or Sundays or weekends that means both Saturday and Sunday. In some countries weekends are Thursday and Friday.

This can be done easily using an array formula. For example we have dates listed from July 7, 2013 to August 6, 2013 in column A from cell 2 to 31.

View Contents [hide]

• Counting specific weekday – Single day in a week
• Bonus Tip: Count Mondays in the date range
• Counting specific weekdays – More than one day

Counting specific weekday – Single day in a week

Suppose in cell D2 you want to count number of Saturdays in the date range. Then put this formula in cell D2:

=SUM(--(WEEKDAY(A1:A31)=7))

And as this is an array formula you have to execute it by pressing Ctrl+Shift+Enter and not just Enter.

So what is happening behind the scene in this SUM, WEEKDAY and two dashes horror!

Tip on understanding formula: Always look at formula from inside out instead of going from left to right if multiple functions have been used. Start from the inside most brackets and then move out solving each function and its operation in your head.

WEEKDAY formula actually gives us a specific number depending on the day from 1 through 7.

1 = Sunday, 2 = Monday, 3= Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday

So in our case 7 is representing Saturday.

So weekday gives us an array of numbers depending on the days in the range. Doesn’t make sense? Read on as I explain what I mean by this one sentence 

Select this part of the formula: WEEKDAY(A1:A31) and then press F9 you will get this:

{1;2;3;4;6;6;7;1;2;3;4;5;6;7;1;2;3;4;5;6;7;1;2;3;4;5;6;7;1;2;3}

Now you see that it is a series of numbers. Putting this series or as it is known an array against a criteria to check if each number is equal to 7 or not we have appended “=7” after WEEKDAY function this will check if it is equal to 7 which in other words mean it is checking if it is Saturday or not.

Select WEEKDAY(A1:A31)=7 part of the formula with your mouse and press F9 you will get this:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}

So we get a collection of TRUEs and FALSEs. Now we only want to count the TRUEs and ignore the FALSEs. To make this easy for Excel we have to convert this logic array into something that Excel can count. The easiest is to convert TRUEs and FALSEs to 1s and 0s. To make this happen we added two dashes “–” before WEEKDAYS formula. This will get us 1s and 0s which we will then add up using SUM function.

Bonus Tip: Count Mondays in the date range

Remember that WEEKDAY returns a number for each day of the week. In order to count Mondays or some other weekday in the date range remember these numbers for each weekday:

1 = Sunday, 2 = Monday, 3= Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday

So if you like to calculate Mondays replace 7 with 2 in the above formula and it will count Mondays for you.

Counting specific weekdays – More than one day

In the above example we calculated a specific day from the range of days like Saturday. But what if you like to count both Saturday and Sunday or Thursday and Friday?

In this case we will use the same formula with a little modification:

In cell D3 put this formula:

=SUM(--(WEEKDAY(A1:A31)={7,1}))

As this is an array formula so execute it by pressing Ctrl+Shift+Enter

So what has happened? Instead of asking Excel to check the array created by WEEKDAY against one number we ask it to check against two numbers i.e. 7 and 1 where 7 = Saturday and 1 = Sunday and it counts 9 occurrences for both days in the range.

Similarly if you want to calculate the number of Thursdays or Fridays replace {7,1} with {5,6}

#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1