Task
Suppose we need to find out exactly how many specific days of the week fall into a given date interval. For example, how many Fridays are there in January 2016.
Solution
The formula we need will look like this:
Don’t forget that this is an array formula (the curly braces in the formula bar remind you of this), i.e. after entering it, you need to press not normal Enterand the combination Ctrl + Shift + Enter.
How this formula works
With external compactness, in fact, several tricky techniques are buried in this formula. Let’s take a look at them.
Each date is perceived by Microsoft Excel as a number-counter, namely, the number of days from the date reference point in Excel (01.01.1900/XNUMX/XNUMX) to the specified date. This number can be seen if you change the date cell format to numeric or generic:
That is, from the point of view of Excel, we need to check the date interval 42370:42400.
At the same time, numbers in Excel denote something else that is much more familiar to everyone – the numbers of rows on a sheet. Try to type in any arbitrary cell, for example = 7:10 – and Excel will refer to the specified lines, highlighting them entirely:
The fragment of our formula B1&”:”&B2 similarly forms the range of entire selected lines from 42370th to 42400th, and the function INDIRECT (INDIRECT) turns the text string “42370:42400” into a real link to the lines with those numbers.
Then the function LINE (ROW) determines the line numbers in this range and gives us an array of numbers {42370, 42371, 42373 … 42400}.
After that we use the function TEXT (TEXT)to determine the day of the week for each date corresponding to our numbers. The second argument to this function, “ddd”, asks Excel for the day of the week for the date as a two-letter abbreviation (Mon, Tue, Wed, and so on).
Finally, the function comes into play IF (IF)A that checks the received day of the week. If it is equal to the required value (Ft), then add one to the total score, otherwise zero.
Here are some tricks
- How Excel actually understands, stores and processes dates
- How to determine if two date intervals intersect?
- How to make a pop-up calendar for convenient date entry in Excel sheet cells