• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / Count How Many Fridays Between Two Dates

Count How Many Fridays Between Two Dates

You may need to count the occurrences of a specific weekday between two dates in Excel. For example, how many Fridays between two dates.

There is no real standalone function in Excel to do this, but it can be done. This could be a useful formula to find how many payments, or how many meetings until an end date.

How Many Fridays Until a Specific Date – Excel Formula

The formula below calculates the number of Fridays between the date in cell A2 and the date in cell B2. The formula is explained below.

=B2-A2-NETWORKDAYS.INTL(A2,B2,16)+1
Calculating the number of Fridays between two dates

In this formula, the start date is subtracted from the end date. This leaves us with how many days in total between the two dates.

In the second half of the formula, the NETWORKDAYS.INTL function is used to calculate the difference between the two dates excluding Fridays. This is then subtracted from the current total to leave us with how many Fridays there are.

The 16 in the function specifies to exclude the Fridays. When typing the function a list appears asking which days to exclude in the weekend argument.

The +1 is added to the end because the NETWORKDAYS.INTL function calculates whole workdays. For example, you may consider the difference in days between today and tomorrow to be 1 day. NETWORKDAYS.INTL would return that answer as 2 as it uses each day as a whole day. So the +1 is added to counteract that.

Wonderful Quick Tip for Achieving This

I wanted to explain another technique to count the number of times a specific day of week occurs between two dates.

The NETWORKDAYS.INTL function is used, and in the third argument a string of 1’s and 0’s are used to specify which day of week to count. The example below counts the number of Fridays again.

The string starts from a Monday. So this can easily be customised to your own requirements.

Entering a 1 specifies a non working day, and 0 specifies a working day. The 0 in the fifth position identifies Friday only as the working day.

=NETWORKDAYS.INTL(A2,B2,"1111011")

More Excel Formula Tutorials

  • Five Awesome Date Functions in Excel
  • Timesheet with Different Rates for Shift Work
  • How Many Months Between Two Dates

Related Posts:

  • Count the Number of Words in a Cell
    Count the Number of Words in a Cell

Reader Interactions

Comments

  1. Chris J says

    5 January 2021 at 7:05 pm

    The Quick Tip works in most cases but if the 1st of the month is the day after the day you want to count (i.e. Saturday in your example) the formula returns a count that is one too high. For example May 2021 has four Fridays but 01 May 2021 is a Saturday, the formula returns 5 for May.
    The following addition to the formula appears to fix the problem
    =NETWORKDAYS.INTL(A2,B2,”1111011″)-IF(WEEKDAY(EOMONTH(B2,-1))=6,1,0)

    Reply
    • Alan Murray says

      6 January 2021 at 7:08 pm

      Thank you for the tip, Chris.
      I just checked the tip on the blog post with the 01/05/2021 and the 30/05/2021 and it successfully returned 4, so I don’t know why it didn’t work for you.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • IF Function in Power Query Including Nested IFS
  • Conditional Formatting Multiple Columns – 3 Examples

Recent Posts

  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
  • Lookup Multiple Values in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·