Skip to main content

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


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 (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)

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


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!

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


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!
Comments (13)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I have 4 variables: 1st: Date of onset, 2nd: Date of termination, 3rd: Date of measurement, 4th: measurement. I want to calculate the average of the 4th variable between each period of onset-termination. How can i do it? I think its a bit tricky. Each date of onset has an counterpart date of termination. The same applies for the 3rd and 4th variable.
This comment was minimized by the moderator on the site
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???
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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..
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations