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

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...

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:

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

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)

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

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:

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

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.

(Video) Write an IF Statement for Dates Between Two Dates (Date Range)

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:

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

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:

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

If you want to have a free trial (30-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)

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

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.

(Video) Determine if a date is on a Weekday or Weekend in Excel

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

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 BooleanSelect Case Weekday(InputDate)Case vbSaturday, vbSundayIsWeekend = TrueCase ElseIsWeekend = FalseEnd SelectEnd 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)

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

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.

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

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:

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

(Video) How to lookup a value if the date fall between two dates | Excel Tutorial

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:

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

Notes:

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

If you want to have a free trial (30-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...

(Video) How to lookup a value if the date fall between two dates | Excel Tutorial

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!

The Best Office Productivity Tools

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

  • Reuse: Quickly insert complex formulas, chartsand 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. 60-day money back guarantee.

Read More... Free Download... Purchase...

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!

Read More... Free Download... Purchase...

FAQs

How do you check if there is a weekend between two dates in Excel? ›

Number of Weekends Between Two Dates in Excel

We can use the NETWORKDAYS function to calculate the number of weekends between two dates. While the Networkdays function calculates the number of working days, we can also use to get the number of weekend days between two dates.

How do you find the difference between two dates and excluding weekends in Excel? ›

Use the NETWORKDAYS.INTL function when you want to calculate the number of workdays between two dates. You can also have it exclude weekends and holidays too.

How do I compare data between two dates in Excel? ›

Comparing Dates in Excel
  1. Comparing Dates in Excel. ...
  2. This can be done using a simple equal-to-operator. ...
  3. Compare Dates Using IF Formula (Greater Less/Less Than) ...
  4. Below is the formula that will do this: =IF(C2<=B2,"In Time","Delayed")

How do I use an IF function in Excel for dates? ›

So the first test logical one would be is this date greater. Than or equal to the start date and I

How do I calculate the difference between two dates and working days in Excel? ›

=NETWORKDAYS(start_date, end_date, [holidays])

The function uses the following arguments: Start_date (required argument) – Start_date can either be earlier than end_date, later than end_date, or same as end_date. End_date (required argument) – The end date.

How do I automatically highlight weekends in Excel? ›

Using WORKDAY Function

Highlighting weekends is doable using the WORKDAY function with Conditional Formatting and you also get the option of adding holidays to the mix. The WORKDAY function returns the serial number that represents the date before or after a given number of workdays.

How do you find the number of weekends between two dates? ›

Then you calculate the amount of weekend days. To do this you have to add the Days Between to the Day of the Week, and then FLOOR that value to get the highest integer result. You then divide that number by 7 to get the amount of WEEKS and then multiply that by 2 to calculate the amount of WeekEnd days.

How do you calculate date and time excluding weekends? ›

Let's start off here and the function you want to use is network days it has three arguments start

How do I calculate Saturday and Sunday in Excel? ›

Excel WEEKDAY function to work with days of week. Microsoft Excel provides a special WEEKDAY function to return the day of the week corresponding to a given date. The result returned by an Excel WEEKDAY formula is an integer, ranging from 1 (Sunday) to 7 (Saturday) by default.

How do you calculate the difference between two dates? ›

The most suitable function to do this kind of calculation is the function date diff. We'll go

How can I compare two dates? ›

For comparing the two dates, we have used the compareTo() method. If both dates are equal it prints Both dates are equal.
...
Using Date. compareTo() Method
  1. 0: if both dates are equal.
  2. A value less than 0: if the date is before the argument date.
  3. A value greater than 0: if the date is after the argument date.

How do I compare dates in a spreadsheet? ›

How to Compare Dates in Google Sheets (With Examples)
  1. Method 1: Check if Dates are Equal =A1=B1.
  2. Method 2: Check if Dates are Not Equal =A1<>B1.
  3. Method 3: Check if First Date is Greater than Second Date =A1>B1.
  4. Method 4: Check if First Date is Less than Second Date =A1<B1.
2 Feb 2022

How do you write an IF THEN statement in Excel with multiple conditions? ›

Another way to get an Excel IF to test multiple conditions is by using an array formula. To complete an array formula correctly, press the Ctrl + Shift + Enter keys together. In Excel 365 and Excel 2021, this also works as a regular formula due to support for dynamic arrays.

Can IF statement have 2 conditions in Excel? ›

The multiple IF conditions in Excel are IF statements contained within another IF statement. They are used to test multiple conditions simultaneously and return distinct values. The additional IF statements can be included in the “value if true” and “value if false” arguments of a standard IF formula.

Is there a between function in Excel? ›

Excel doesn't have an explicit BETWEEN function, but you can create your own using other formulas in the program. For example, if you want to check whether your gross income from sales in 2021 is between your sales totals for 2019 and 2020, you could use a BETWEEN formula.

How do I calculate working days excluding Sundays in Excel? ›

Count days excluding Sundays with formula

Here I can introduce a formula for counting days excluding Sundays during a date range. Select a blank cell, here is C2, and type this formula =B2-A2-INT((B2-A2-WEEKDAY(B2)+1)/7) into it, and then press Enter key, a date displayed.

How do I highlight columns in Excel with weekend days? ›

Step 1: Prepare the worksheet, including the activities, date for the activities, day and what to be returned by the formula. Step 2: Apply conditional formatting to the date column (B), as we shall want the weekends to be highlighted in a different color. Step 4: Press Enter to get either TRUE or FALSE.

How do you define a weekend in Excel? ›

The Excel NETWORKDAYS. INTL function calculates the number of working days between two dates. NETWORKDAYS. INTL can optionally exclude a list of holidays and provides a way to specify which days of the week are considered weekends.

How do I highlight weekends and holidays in Excel? ›

Highlight weekends and holidays with Conditional Formatting
  1. Click Use a formula to determine which cells to format from the Select a Rule Type list box;
  2. Type this formula: =NETWORKDAYS($A2,$A2,$F$2:$F$6)=0 into the Format values where this formula is true textbox;
  3. And then, click Format button.

How does WEEKDAY function work in Excel? ›

The Excel WEEKDAY function takes a date and returns a number between 1-7 representing the day of week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable. You can use the WEEKDAY function inside other formulas to check the day of week.

How do you calculate net work hours between two dates excluding weekends or holidays in Excel? ›

The Excel NETWORKDAYS function calculates the number of working days between two dates. NETWORKDAYS automatically excludes weekends (Saturday and Sunday) and can optionally exclude a list of holidays supplied as dates.

What does exclude weekends mean? ›

When you exclude weekend in sales campaign, the campaign pauses on Saturday and Sunday. The day count of the campaign resumes on Monday.

How do I compare two dates in Datepicker? ›

});
  1. function getDate( element ) { var date;
  2. try { date = $. datepicker. ...
  3. } catch( error ) { date = null;
  4. } return date;
  5. } function calcDiff() {
  6. var d1 = $('#booking_from_date'). datepicker('getDate'); ...
  7. var diff = 0; if (d1 && d2) {
  8. diff = Math.floor((d2.getTime() - d1.getTime()) / 86400000); // ms per day. }

How can I compare current date and time with another date and time in Android? ›

how to compare current date and time with another date and time...
  1. Date date1 = calendar1. getTime();
  2. Date date2 = calendar2. getTime();
  3. if (date1. compareTo(date2) > 0)
  4. { Log. i("app", "Date1 is after Date2");}
  5. else if (date1. compareTo(date2) < 0)
  6. { Log. ...
  7. else if (date1. ...
  8. { Log.
28 Apr 2020

How do I compare two dates in R? ›

Using '-' operator

The date objects can be compared using the minus operator to subtract the smaller date from the larger date. The units in which the date is returned is the maximum of the hours, minutes or seconds.

Does index match work with dates? ›

All the dates normally should turn into those 5 digit numbers (if they were originally formatted as general for example). You simply then need to format them as date with the appropriate format, and your index/match should be fine matching the two date columns.

How do you check if a date is after another date in Excel? ›

Type the specified date 2015/3/10 in a blank cell such as cell F2. 2. Select a blank cell which is corresponding to the first date cell in the Date column such as G2. Then copy and paste formula =IF(A2>$F$2, "YES", "NO") into the Formula Bar and press the Enter key.

What is the Sumifs function in Excel? ›

The SUMIFS function, one of the math and trig functions, adds all of its arguments that meet multiple criteria. For example, you would use SUMIFS to sum the number of retailers in the country who (1) reside in a single zip code and (2) whose profits exceed a specific dollar value.

How do you count the number of weekends between two dates? ›

Then you calculate the amount of weekend days. To do this you have to add the Days Between to the Day of the Week, and then FLOOR that value to get the highest integer result. You then divide that number by 7 to get the amount of WEEKS and then multiply that by 2 to calculate the amount of WeekEnd days.

How do I get Saturday and Sunday in Excel? ›

When given a date, WEEKDAY returns a number 1-7, for each day of the week. In it's standard configuration, Saturday = 7 and Sunday = 1. By using the OR function, use WEEKDAY to test for either 1 or 7. If either is true, the formula will return TRUE and trigger the conditional formatting.

How do you calculate working days in Excel excluding weekends and holidays? ›

Select a blank cell and type this formula =WORKDAY(A2,B2), and press Enter key to get result. Tip: In the formula, A2 is the start date, B2 is the days you want to add. Now the end date which add 45 business days excluding weekends has been shown.

How do I count the number of weekends in Excel? ›

To filter weekdays or weekend days, you apply Excel's filter to your table (Data tab > Filter) and select either "Workday" or "Weekend".

How do you calculate working days between two dates? ›

The working days and non-working list so a working day would be specified with a zero and a

How do I count Saturdays in a month in Excel? ›

If it's January we need 31 rows or 31 dates if it's February. We need 28. So we use the end of the

How do you define a weekend in Excel? ›

The Excel NETWORKDAYS. INTL function calculates the number of working days between two dates. NETWORKDAYS. INTL can optionally exclude a list of holidays and provides a way to specify which days of the week are considered weekends.

What function can you use to determine whether a day is a WEEKDAY? ›

The WEEKDAY Function[1] is an Excel DATE and TIME Function. The function will return an integer that is a representation of the day of the week for a given date. WEEKDAY is quite useful in financial analysis.

How do you calculate working days excluding weekends? ›

First two arguments are the same start date. And end date and the weekend. Gives you a list of non-

How do I calculate working days excluding Sundays in Excel? ›

Count days excluding Sundays with formula

Here I can introduce a formula for counting days excluding Sundays during a date range. Select a blank cell, here is C2, and type this formula =B2-A2-INT((B2-A2-WEEKDAY(B2)+1)/7) into it, and then press Enter key, a date displayed.

Videos

1. How to VLOOKUP to Return Value if Date Falls between Two dates in Excel
(ExcelHow Tech)
2. How to Calculate Working Days in Excel & Exclude ANY Days you WANT (weekends too)
(Leila Gharani)
3. Excel BETWEEN Formula Trick - Find if a date/value falls between a range!
(MyOnlineTrainingHub)
4. Excel 2016 Highlight If Date Is Weekend And Weekday
(Haritha Computers & Technology)
5. How to Highlight a Date between Two Dates in Excel
(usemybox)
6. How to Calculate number of Working Days between two Dates in Excel 2016
(Find Easy Solution)
Top Articles
Latest Posts
Article information

Author: Van Hayes

Last Updated: 02/10/2023

Views: 5792

Rating: 4.6 / 5 (66 voted)

Reviews: 81% of readers found this page helpful

Author information

Name: Van Hayes

Birthday: 1994-06-07

Address: 2004 Kling Rapid, New Destiny, MT 64658-2367

Phone: +512425013758

Job: National Farming Director

Hobby: Reading, Polo, Genealogy, amateur radio, Scouting, Stand-up comedy, Cryptography

Introduction: My name is Van Hayes, I am a thankful, friendly, smiling, calm, powerful, fine, enthusiastic person who loves writing and wants to share my knowledge and understanding with you.