Count Day of Week

Between Dates in Excel

(How many Fridays?)

How can you figure out how many Friday’s are included between two days?

For example, if our start date is December 3, 2018 and our end date is December 22, 2018 the answer should be 3.

What do you think?  How can we solve this in Excel?

Excel does not provide a formula that calculates the number of Friday’s between two days, but we can use existing functions and formulas to get the result we want.

Part 1 – Determine Number of Days Between Two Dates

An easy, and common strategy to determine the number of days between two dates is to subtract the startdate from the enddate.

Let’s create a formula that takes the enddate (B5) and deducts the startdate (B4).  This way we can get the number of days that are between these two dates.

Now remember, dates are just numbers in Excel.  If we switch the number formatting from Date to General, we see the numbers associated with these dates.

Dates start counting from January 1, 1900.  This means that December 3, 2018 is the 43,437th day since January 1, 1900.

If we change cell B4 to 1 and restore the formatting to a Date style, we see January 1, 1900.

Part 2 – Determine Number of Days Between Two Dates – Less Fridays

Another formula that calculates the number of days between two dates and excludes certain days from our calculation, is the NETWORKDAYS formula.  NETWORKDAYS originally assumed that the days it should exclude are the weekends, so the original NETWORKDAYS formula just requires the startdate and the enddate; it automatically excludes weekends, and it assumes that weekends are Saturdays and Sundays.

Not all countries observe their weekends on Saturday and Sunday (many Middle East countries observe Friday/Saturday as their weekend), and not everyone takes 2-day weekends.  Because of this, beginning in Excel 2010 we were provided with an updated version of the formula called NETWORKDAYS.INTL (international).  With NETWORKDAYS.INTL we can define the type of weekend that we have in our country as well as a 1-day or 2-day (sequential) weekend.

For our startdate we will use 12/3/2018 and our enddate will use 12/22/2018.  For our weekend we can pick Saturday, Sunday or we can scroll down and pick “Friday Only”.

If we wanted to exclude Monday’s only, we would select 12, but in this case, we wish to exclude all Fridays, so we’re going select 16.  What this does is counts the number of days between the two dates and while excluding Fridays.

We can use the information from these two formulas to calculate how many Friday’s we have, but there’s one thing we must be careful with and that’s what these days include and exclude.

When we get the difference between these two days, are we including the startdate and the enddate or is one of them, or both, excluded?  That is something we must take care of when deducting one from the other.

To check that out, let’s restrict our date range to a smaller range so we can quickly look at the calendar and find out what’s included and excluded.

Let’s switch the startdate to the 3rd (Monday) and the enddate to the 6th (Thursday).  There are no Friday included, but look at our results.

We get different answers.  The formula in cell B7 states that there are 3 days between the startdate and enddate?  One of these days is not included.  The formula in cell B7 is excluding the startdate, whereas the NETWORKDAYS.INTL in cell B8 includes both the startdate and the enddate.

To be able to compare them to one another, we need to make an adjustment to the formula in cell B7.  We’re going to add a 1 to result.  This will ensure we are including the startdate and enddate.

Part 3 – Determine the Difference

Now we have the necessary information to calculate how many Fridays are between these two days.

We have how many days are in between these two dates (B7); we have how many days are between these two dates but excluding Fridays (B8).  All we must do is subtract one number (B7) from the other (B8).

If we switch the enddate to December 8th, we get 1; from the 3rd to the 8th there’s 1 Friday.

If we switch the enddate to December 22nd, we get 3.

Part 4 – Perform the Logic in a Single Formula

Let’s consolidate the logic into a single formula.  The logic is as follows:

  1. Subtract the startdate from the enddate
  2. Add 1 to compensate for the missing startdate
  3. Deduct the number of days without Fridays

We get a date because Excel thinks we want a date.  We don’t want a date, we just want a number.  Let’s switch the formatting style of cell B1 to the General format.

Testing a Larger Date Range

Let’s look at a bigger timeline.  We will switch cell B5 to January 31, 2019; we get 8 Fridays between the two dates.

The NETWORKDAYS.INTL formula, along with other date formulas, are covered in my FREE eBook called “Top 10 Essential Excel Formulas to Work Smarter”.  If you are interested in this FREE eBook, click the link below.

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Excel Dashboards that Inform & Impress

Use these techniques in your own reports

Unbeatable value!

LEARN MORE

3 Comments

  1. Damien Mazzone January 15, 2019 at 2:51 am - Reply

    An interesting approach. I attempted the “Count Fridays” challenge prior to looking at your solution and ended up going a different way. One could also achieve the count as follows:
    =SUMPRODUCT((TEXT(date_range,”ddd”)=”fri”)*(date_range>=start_date)*(date_range=start_date)*(date_range<=end_date))

    Now weekdays are simply converted to numbers (WEEKDAY is set to give Friday = 5) and the solution becomes language-independent.

    • Damien Mazzone January 24, 2019 at 4:04 am - Reply

      Oddly the formulae don’t appear to have come through correctly. The upshot is this:
      “=SUMPRODUCT((WEEKDAY(date_range,2)=5)*(date_range>=start_date)*(date_range<=end_date))"

      Language-independent but does require dates to be in a list.

      • Damien Mazzone January 24, 2019 at 4:36 am - Reply

        This appears to work too without the need for a list of dates:
        “=INT((end_date-start_date+1)/7)+IF(WEEKDAY(start_date,2)>5,0,1)”

Leave A Comment

Share This