Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel MMULT Function
  • Extract data with helper column in Excel
  • Offset in Excel
  • How to use Excel INDIRECT Function
  • How to calculate two-way lookup VLOOKUP in Excel Table

Data Analysis Examples

  • Conditional Formatting Rules in Excel
  • How To Filter Data in Excel
  • Add Outline to Data in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • How to perform a t-Test in Excel

Data Validation Examples

  • Excel Data validation allow weekday only
  • Excel Data validation allow uppercase only
  • Excel Data validation with conditional list
  • Excel Data validation date in specific year
  • How To Create Drop-down List in Excel

How to calculate next scheduled event in Excel

by

To get the next scheduled event from a list of events with dates, you can use an array formula based on the MIN and TODAY functions to find the next date, and INDEX and MATCH  to display the event on that date.

Formula

{=MIN(IF((range>=TODAY()),range))}

Note: this is an array formula and must be entered with Control + Shift + Enter.

Explanation

In the example shown, the formula in G6 is:

{=MIN(IF((date>=TODAY()),date))}

Where “date” is the named range D5:D14.

How this formula works

The first part of the solution uses the MIN and TODAY functions to find the “next date” based on the date today. This is done by filtering the dates through the IF function:

IF((date>=TODAY()),date)

The logical test generates an array of TRUE / FALSE values, where TRUE corresponds to dates greater than or equal to today:

{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

When a result is TRUE, the date is passed into array returned by IF. When a result is FALSE, the date is replaced by the boolean FALSE. The IF function returns the following array to MIN:

Worked Example:   Extract multiple matches into separate columns in Excel
{FALSE;FALSE;FALSE;43371;43385;43399;43413;43427;43441;43455}

The MIN function then ignores the FALSE values, and returns the smallest date value (43371), which is the date Sept. 28, 2018 in Excel’s date system.

Getting the movie name

To display the movie associated with the “next date””, we use INDEX and MATCH:

=INDEX(movie,MATCH(G6,date,0))

Inside INDEX, MATCH finds the position of the date in G6 in the list of dates. This position, 4 in the example, is returned to INDEX as a row number:

=INDEX(movie,4)

and INDEX returns the movie at that position, “The Dark Knight”.

Worked Example:   Position of first partial match in Excel

All in one formula

To return the next Movie in a single formula, you can use this array formula:

{=INDEX(movie,MATCH(MIN(IF((date>=TODAY()),date)),date,0))}

With MINIFS

If you have a newer version of Excel, you can use the MINIFS function instead of the array formula in G6:

=MINIFS(date,date,">="&TODAY())

MINIFS was introduced in Excel 2016 via Office 365.

Handling errors

The formula on this page will work even when events aren’t sorted by date. However, if there are no upcoming dates, the MIN function will return zero instead of an error. This will display as the date “0-Jan-00” in G6, and the INDEX and MATCH formula will throw an #N/A error, since there is no zero-th row to get a value from. To trap this error, you can replace MIN with the SMALL function, then wrap the whole formula in IFERROR like this:

={IFERROR(SMALL(IF((date>=TODAY()),date),1),"None found")}

Unlike MIN, the SMALL function will throw an error when a value isn’t found, so IFERROR can be used to manage the error.

Worked Example:   How to return blank in place of #DIV/0! error in Excel

Post navigation

Previous Post:

How to calculate most recent day of week in Excel

Next Post:

How to calculate next day of week in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to Remove Leading Spaces in Excel
  • REPT function: Description, Usage, Syntax, Examples and Explanation
  • How to extract word containing specific text in Excel
  • How to display conditional message with REPT function in Excel
  • Split text and numbers in Excel
  • Convert date to Julian format in Excel
  • List holidays between two dates in Excel
  • DAY function: Description, Usage, Syntax, Examples and Explanation
  • Get age from birthday in Excel
  • Add months to date in Excel
  • Calculate payment periods for loan in Excel
  • NOMINAL function: Description, Usage, Syntax, Examples and Explanation
  • MIRR function: Description, Usage, Syntax, Examples and Explanation
  • TBILLYIELD function: Description, Usage, Syntax, Examples and Explanation
  • MDURATION function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles