# 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,437^{th} 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 **3 ^{rd}**

*(Monday)*and the

**enddate**to the

**6**

^{th}*(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 8**^{th,} we get **1**; from the 3rd to the 8th there’s 1 Friday.

If we switch the **enddate** to **December 22 ^{nd}**, 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:

- Subtract the
**startdate**from the**enddate** - Add
**1**to compensate for the missing**startdate** - 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 “T*op 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 Dashboards that Inform & Impress**

Damien MazzoneJanuary 15, 2019 at 2:51 amAn 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 MazzoneJanuary 24, 2019 at 4:04 amOddly 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 MazzoneJanuary 24, 2019 at 4:36 amThis 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)”