Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to determine if a date falls between two dates or on weekend in Excel?

Sometimes you may want to determine or check whether a date falls between two dates or weekend in Excel. This article will show you following tricky ways to check whether a date falls between two dates or weekend in Excel.

Determine if a date falls between two dates with formula
Easily determine if a date falls between two dates with an amazing tool  
Determine if a date falls on a weekend with formulas and VBA code
Determine if a date falls on a weekend with an amazing tool

More tutorials for dates...


Quickly determine if a date falls between two given dates:

The Select Specific Cells utility of Kutools for Excel can help you quickly determine if dates in a specific range are falling between two given dates. Find them and select them easily. Download the full feature 60-day free trail of Kutools for Excel now!

doc date falls between two dates


Determine if a date falls between two dates in Excel

Supposing you need to determine if the dates in Column A fall between 7/1/2008 and 12/31/2009. please do as follows:

1. In a blank cell, says Cell B2, copy and paste the below formula into it and press the Enter key.

=IF(AND(A2>$B$1,A2<$c$1),A2, FALSE)

Note: This formula will check whether the date falls between 7/1/2008 and 12/31/2009. If the date falls in this period, it will return the date; if the date does not fall in this period, it will return the text of FALSE.

2. Keep selecting the result cell, drag the Fill Handle down to get all results. See screenshot:

Now you can identify whether a date falls within a specified date range or not.


Determine if a date falls between two dates in Excel with Kutools for Excel

Here recommend you an amazing tool - the Select Specific Cells utility of Kutools for Excel. This tool helps you not only find out all dates that are falling between two given dates, but also select all qualified date cells immediately.
Before applying it, you need to download and install it firstly.

1. Select the range with dates you want to determine if they fall between two dates, and then click Kutools > Select > Select Specific Cells. See screenshot:

2. In the Select Specific Cells dialog box, select the Cell option in the Selection type section, and then specify the Greater than and Less than dates, and finally click the OK button.

You can see date cells which fall between two dates are selected immediately. See screenshot:

  If you want to have a free trial (60-day) of this utility, please click to download it, and then go to apply the operation according above steps.


Determine if a date falls on a weekend with formulas and VBA code

You can determine whether a date in Column A falls on weekends with following steps:

Method A: Using formula to check whether a date falls on a weekend.

1. In a blank cell, copy and paste the below formula into it and press the Enter key.

=IF(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7),A2,FALSE)

This formula will identify whether a date falls on weekends or not. If the date falls on weekend, it will return the date; if the date does not fall on weekend, it will return the text of FALSE.

2. Keep selecting the result cell, then drag the Fill Handle down to get all results.

Method B: Using User-defined function to check whether a date falls on a weekend.

1. Press the ALT + F11 keys togetherto open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert >> Module, and paste the following macro into the Module Window.

Public Function IsWeekend(InputDate As Date) As Boolean
Select Case Weekday(InputDate)
Case vbSaturday, vbSunday
IsWeekend = True
Case Else
IsWeekend = False
End Select
End Function

3. Press Alt + Q keys simultaneously to close the Microsoft Visual Basic for Applications window.

4. In a blank cell, enter formula into the Formula Bar, and then press the Enter key.

=IsWeekend(A2)

If it returns the text of True, the date in Cell A2 is a weekend; and if it returns the text of False, the date in Cell A2 does not fall on a weekend.


Determine if a date falls on weekend with an amazing tool

Actually you can convert all dates to weekday name, and then check for weekends based on Saturday or Sunday. Here the Apply Date Format utility of Kutools for Excel can help you easily solve the problem.
Before applying it, you need to download and install it firstly.

1. Select the date range, and then click Kutools > Format > Apply Date Formatting. See screenshot:

2. In the Apply Date Formatting dialog box, select Wednesday in the Date formatting box, and then click the OK button.

Now the selected dates are converted to weekdays immediately. You can determine if a date falls on a weekend or not directly based on its content. See screenshot:

Notes:

  • The converted results worked directly in the original data;
  • This utility support UndoCtrl + Z”.

  If you want to have a free trial (60-day) of this utility, please click to download it, and then go to apply the operation according above steps.


Related articles:

Convert date to weekday, month, year name or number in Excel
Says you enter a date in one cell, and it shows as 12/13/2015. Is there a way to show only the month or the weekday, or the text of month name or weekday name, such as December, or Sunday? The methods in this article can help you easily convert or format any kinds of date to display only the weekday name or month name in Excel.

Quickly convert birthdate to age in Excel
For instance, you get a range of various birthdate data in Excel, and you need to convert these birthdate to display their exact age value in Excel, how would you like to figure out? This article lists some tips to convert the birth date to age in Excel easily.

Compare dates if greater than another date in Excel
Supposing you have a list of dates, and want to compare these dates with a specified date in order to find out the date which is greater than that specified date in the list, what would you do? In this article, we will show you methods of comparing dates if greater than another date in Excel.

Sum values between two dates range in Excel
When there are two lists in your worksheet, one is the list of dates, the other is the list of the values. And you want to sum up the values between two dates range only, for instance, sum up the values between 3/4/2014 and 5/10/2014, how can you calculate them quickly? The methods in this article will do you a favor.

Add days to date including or excluding weekends and holidays in Excel
This article is talking about adding days to a given date excluding weekends and holidays which means to add business days (from Monday to Friday) only in Excel.

More tutorial for dates...


Determine if a date falls between two dates or weekend with Kutools for Excel

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Nathali · 1 years ago
    I have two tables. The first table has a date and an ID and the other table has a range dates, the same ID and, and one code. In two tables repeat the ID because each has different information. I need get in the table number one the code according with the ID and range dates in the second table. But i don't know how???
  • To post as a guest, your comment is unpublished.
    Dani · 2 years ago
    I want to find all projects that fall within today's date +30 days (Within a month) and have the Project Number displayed when in the range, if not in range keep blank
  • To post as a guest, your comment is unpublished.
    Albert MUKIZA · 3 years ago
    i want to monitor the dates and set the the alert in my excel , example i want to set in my excel to find the difference days between the request date and deadline to order date and automatically compare this difference with the policy date if the difference is greater than policy date , excel consider not done with an alert color , and if the difference is less than the policy days , excel consider the done with alert color. thank you
  • To post as a guest, your comment is unpublished.
    Dwarkanath bari · 3 years ago
    Hello to all,
    I have a requirement in excel i.e.
    i want to generate dates if design actual date is delay for 2 days then for costing department date is increase by delay date.

    design target actual complete costing target marketing target

    05-04-2016 07-04-2016 09-04-2016 10-04-2016

    in above design target date is 05/04/2016 but design task completed on 07-04-2016 so costing target date will be automatically calculated as per delay days.
  • To post as a guest, your comment is unpublished.
    zoe · 3 years ago
    I am recording the SLA duration between dates
    40 day SLA starting on day XXXX
    I am looking for a formula to show if client holds for x number of days that comes off the SLA too
    ie
    01 june 2016 + 40 days = 11 July 2016 but project finishes 15 July shows as 4 days overdue

    However
    01 June 2016 Start
    15 July 2016 End date of project
    client Held 7 Day
    3 days before SLA

    Any help?
  • To post as a guest, your comment is unpublished.
    Sarathraj r · 3 years ago
    I want to show a certain date in a cell from a date range which is between two dates.
    eg: 01-05-2016 to 05-05-2016 may shown as in other cell as 01-05-2016 .

    pls help me..
  • To post as a guest, your comment is unpublished.
    Gary Devletian · 4 years ago
    My question falls along these lines.
    I have two date ranges A through B, and X through Y.
    I need to conditionally format a cell if any dates in range A-B fall within range X-Y.
  • To post as a guest, your comment is unpublished.
    KANNAN · 4 years ago
    Using IF Function i need the following formula. Any one can assist

    On A2 cell I will provide a date. On B2 it should indicate 2 Days after A2. If the second day falls on Monday to Friday it should indicate the actual date. If the second day falls on Saturday or sunday it should indicate on Monday automatically

    On the same way I need third day,fourth day and fifth day using Macros. Also advice to how to create macros in Excel.
  • To post as a guest, your comment is unpublished.
    BIJEESH.P · 5 years ago
    I want to know how many times a date or how many dates from a list are present in a range of dates.
    For example
    Date Range is 1-1-2014 31-1-2014.
    The date list is
    05-01-2014
    11-01-2014
    19-01-2014
    08-02-2014

    want to know the excel formula for count how many times the above list of dates present in the above range of dates.
  • To post as a guest, your comment is unpublished.
    Paolo · 5 years ago
    Hi, very helpful info. Im trying to do a range date let me give you the example if payment is made between " Jan. 1-16 the answer should be 7th of the following month and if payment falls Jan. 17-31 excel should be able to answer 21st of the following month. how could i create this kind of fomula
  • To post as a guest, your comment is unpublished.
    Lily · 6 years ago
    I want to do the following:

    I'm in cell w8 which is a empty cell

    Cell L8 contains a date

    if cell L8 equals 12/14/2013 I want it to return the value of 26 however if it contains the date 12/28/2013 ; if cell L8 doesn't contain either date then I want it to go to cell V8 and give me that cell value.

    How would I create the formula?
  • To post as a guest, your comment is unpublished.
    Lily · 6 years ago
    I'm tryin to see if cell L8 contains a specific date if it doesn then there is one of two values I want it to give me; if it doesn't contact neither of the dates then bring back the valude from a totally different cell.
    For example. I'm in an empty cell i.e. W8 and want to look at cell L8 which contains a date and if it equals 12/14/2013 then I want it to return the value of 26, however if the date in cell L8 equals 12/28/2013 then it to return the value of 25 if cell L8 has any other date than the above then I want it to return the value from cell V8. How would I write this???