Excel Formula: Find Next Date In Schedule List
Supposing you have a table with two columns in Excel, one contains the schedule contents, the other contains the schedule date, now you want to find the next date in the schedule based on today, which formula can you use?
If you want to follow along with this tutorial, please download the example spreadsheet.
Generic formula:
| MIN(IF(date_range>=TODAY(),date_range)) |
Syntaxt and Arguments
| Date_range: the cells that contain the schedule dates. |
Return Value
The formula returns 5-digit number. In Excel, the date is stored as serial numbers for better calculation, you can format the 5-digit number as date format if you want to view the date directly.
How this formula works
In the table B3:C8, the schedule dates are listed in C3:C8, the schedule contents are listed in B3:B8, today is 2/23/2021, to find the next date in schedule list, please use below array formula:
| =MIN(IF($C$3:$C8>=TODAY(),$C$3:$C$8)) |
Press Shift + Ctrl + Enter keys to get the correct result.
Then keep the result selected, go to Home tab, and click the drop-down list of Number Format to choose the Short Date or Long Date as you need.
Now the next date in schedule list has been found.
Explanation
IF function: used to test for a specific condition and return TRUE or FALSE.
MIN function: to find the smallest value in a range or an array.
=MIN(IF($C$3:$C8>=TODAY(),$C$3:$C$8))
=MIN{FALSE;44260;44282;44321;44395;44397}
=44260
Tips
If you want to get the schedule contents based on the found date, please use below formula
=INDEX($B$3:$B$8,MATCH(F2,$C$3:$C$8,0))
Press Enter key.
If you want to directly find the next schedule contents based on today, please ues the array formula:
=INDEX($B$3:$B$8,MATCH(MIN(IF($C$3:$C8>=TODAY(),$C$3:$C$8)),$C$3:$C$8,0))
Press Shift + Ctrl + Enter keys.
Relative Formulas
- Get First Day Or Last Day Of Month From A Date
In this tutorial, it provides formulas to return the first day or last day of month from the given date in Excel. - Excel Formula: Find Earliest Date And Latest Date For Each Group
In this tutorial, it provides formulas to find the earliest date and latest date for each group in Excel. - Gount days between two days
To count the number of days or months or years between two given dates will be easy in Excel after you reading this tutorial - Create weekly date range
In this tutorial, it provides a formula to quickly get the weekly date range in Excel.
Relative Functions
- Excel WEEKNUM Function
Returns the week number of the given date in a year - Excel WORKDAY Function
Adds workdays to the given start date and returns a workday - Excel WEEKDAY Function
Get day of the week as a number (1 to 7) from date - Excel DAY Function
DAY function gets the day as a number (1 to 31) from a date - Excel NOW Function
Get the current time and date
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
