+ Reply to Thread
Results 1 to 6 of 6

How to check for holidays between two dates?

  1. #1
    Registered User
    Join Date
    06-05-2020
    Location
    Denmark
    MS-Off Ver
    2010
    Posts
    3

    How to check for holidays between two dates?

    How do I check, if there is a holiday between two dates?

    I have two sheets: one with a 'Start' date and a 'End' date; another with a table containing a list with holiday dates.
    My goal is to 'highlight' the Start and End dates with conditional formatting, if one or more holidays falls between them.

    It would be handy as well to get a listing of those holidays in the cell next to it.
    But that is another puzzle for me to look into.

    I've looked around the net, but only seem to find formulas for checking if a specific date is between two dates.
    I'm also not sure about what to look for, to solve this problem.

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: How to check for holidays between two dates?

    Hi
    how is Excel suppose to know a date is a holiday without entering those dates somewhere ?

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: How to check for holidays between two dates?

    With A1,A2 is start, end date
    F2:F3 is holidays range
    To count holidays between 2 dates (ignore weekend: if holiday fall into weekend, it still be counted)
    Please Login or Register  to view this content.
    For Conditional formatting, using logical statement: Formula>0
    Quang PT

  4. #4
    Registered User
    Join Date
    06-05-2020
    Location
    Denmark
    MS-Off Ver
    2010
    Posts
    3

    Re: How to check for holidays between two dates?

    Quote Originally Posted by arthurbr View Post
    Hi
    how is Excel suppose to know a date is a holiday without entering those dates somewhere ?
    That's why I have a list with holiday dates as mentioned in my question.

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: How to check for holidays between two dates?

    Or try:

    =COUNTIFS(List_holiday,">="&Start date,List_holiday,"<="&End date) > 0

  6. #6
    Registered User
    Join Date
    06-05-2020
    Location
    Denmark
    MS-Off Ver
    2010
    Posts
    3

    Re: How to check for holidays between two dates?

    Quote Originally Posted by bebo021999 View Post
    With A1,A2 is start, end date
    F2:F3 is holidays range
    To count holidays between 2 dates (ignore weekend: if holiday fall into weekend, it still be counted)
    Please Login or Register  to view this content.
    For Conditional formatting, using logical statement: Formula>0
    Quote Originally Posted by Phuocam View Post
    Or try:

    =COUNTIFS(List_holiday,">="&Start date,List_holiday,"<="&End date) > 0
    Thanks! Both worked fine.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Counting Holidays Between Two Dates
    By 1Time in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-22-2017, 11:10 AM
  2. [SOLVED] Generate list of dates between dates based on criteria - Onsite/Offshore holidays
    By sreeks in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-21-2017, 12:37 AM
  3. [SOLVED] Count Unique Number of Dates, Excluding Designated Holidays and Weekend Dates
    By Winship in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-07-2017, 08:37 AM
  4. [SOLVED] Adding Holidays between two dates
    By dharvey078 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-13-2017, 09:46 PM
  5. Is there a faster way to check holidays NETWORKDAYS() replacement
    By squiggler47 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-16-2009, 12:27 PM
  6. Dates with Holidays question
    By HJ in forum Excel General
    Replies: 1
    Last Post: 03-30-2006, 10:15 PM
  7. Check for holidays
    By poppy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-08-2005, 03:13 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1