Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Number and Text Filters Examples in Excel
  • Data Series in Excel
  • Error Bars in Excel
  • How to create a Histogram in Excel
  • Add Outline to Data in Excel

References

  • How to get relative row numbers in a range in Excel
  • Create hyperlink with VLOOKUP in Excel
  • How to get first row number in range in Excel
  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions

Data Validations

  • Prevent invalid data entering in specific cells
  • Excel Data validation exists in list
  • Excel Data validation date in next 30 days
  • Excel Data validation allow weekday only
  • Excel Data validation require unique number

Count holidays between two dates in Excel

by

This tutorial shows how to Count holidays between two dates in Excel using example below.

To count holidays that occur between two dates, you can use the SUMPRODUCT function.

 Formula

=SUMPRODUCT((holidays>=start)*(holidays<=end))

Explanation

In the example shown, the formula in F8 is:

=SUMPRODUCT((B4:B12>=F5)*(B4:B12<=F6))

How this formula works

This formula uses two expressions in a single array inside the SUMPRODUCT function.

The first expression tests every holiday date to see if it’s greater than or equal to the start date in F5:

(B4:B12>=F5)

This returns an array of TRUE/FALSE values like this:

{FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE}

The second expression tests every holiday date to see if it’s less than or equal to the end date in F6:

(B4:B12<=F6)

which returns an array of TRUE/FALSE values like this:

{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE}

The multiplication of these two arrays automatically coerces the TRUE/FALSE values to ones and zeros, resulting in arrays that look like this:

=SUMPRODUCT(({0;0;0;0;1;1;1;1;1})*({1;1;1;1;1;1;1;1;0}))

After multiplication, we have just one array like this:

=SUMPRODUCT({0;0;0;0;1;1;1;1;0})

Finally, SUMPRODUCT sums the items in the array and returns 4.

Holidays on weekdays only

To count holidays that occur on weekdays only (Mon-Fri), you can extend the formula like this:

=SUMPRODUCT((range>=F5)*(range<=F6)*(WEEKDAY(range,2)<6))

Post navigation

Previous Post:

Get day from date in Excel

Next Post:

Calculate loan interest in given year in Excel

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • Nested IF function example in Excel
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • Excel If, Nested If, And/Or Criteria Examples
  • OR function Examples in Excel

Date Time

  • Get days between dates ignoring years in Excel
  • Extract date from a date and time in Excel
  • DAYS360 function: Description, Usage, Syntax, Examples and Explanation
  • Get days between dates in Excel
  • Get month from date in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • How to randomly assign people to groups in Excel
  • Group numbers with VLOOKUP in Excel
  • Running count group by n size in Excel
  • Map inputs to arbitrary values in Excel

General

  • Freeze and Unfreeze Panes in Excel
  • How to password protect excel sheet?
  • Common Errors in Excel
  • 231 Keyboard Shortcut Keys In Excel
  • How to calculate percent of goal in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning