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:
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
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 Function3. 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 Undo “Ctrl + Z”.
Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy permanently free AI features! Download now!
Determine if a date falls between two dates or weekend with Kutools for Excel
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.
Best Office Productivity Tools
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...
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in