We can count day of week between dates in Microsoft Excel. Doing it manually can be very frustrating. We can extract the results automatically. The functions used to count days of week between dates are – ROW, WEEKEND, INDIRECT and SUMPRODUCT. In this tutorial, we will learn how to count day of week between dates in Excel.
Figure 1. Example of How to Count Day of Week Between Dates
Excel can easily count the day of week between dates. For example, if we have two dates, January 1, 2019, and January 28, 2019. We can find out how many Saturday exists between these two dates.
Generic Formula
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(date1&":"&date2)))=dow))
How this formula works
The main purpose of this function is to extract the number of time a day comes between two certain dates. The formula consists of four functions. Here, WEEKDAY searches if the number of mentioned dates fall on the certain day of the week. Excel abbreviates this day of the week as DOW. This formula gives a number which is between one to seven indicating a certain day from the week. Excel counts the days of the week as-
1- Sunday,
2- Monday
3- Tuesday
4- Wednesday
5- Thursday
6- Friday
7-Saturday
Excel works with dates as serial numbers starting from Jan 1, 1900. The INDIRECT and ROW functions work together to create an array of numbers from the dates. The INDIRECT function helps to interpret the concatenated numbers to count as rows. The ROW function returns an array consisting these numbers. These numbers are converted to dates with the WEEKDAY function. Finally, SUMPRODUCT processes these dates and returns the count.
Setting up Data
The following example consists some sample dates. Column A and B have these dates. Column C contains the day of the week represented as numbers.
Figure 2. Setting up the Data
To count the day of week in column D, we need to:
- Go to cell D2.
- Assign the formula
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)))=C2))to D2. - Press Enter to apply the formula to D2.
- Drag the formula from cells D2 to D6 to copy the formula to the entire column.
Figure 3. Applying the Formula to the Data
This will show the day of week between dates in column D. For example, cell D2 will get display the result 4. This means there are four Fridays between 12th December, 2017 and 6th January,2018.
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment