How to Extract a Date From Text in Excel

To extract a date from text in Excel, you find where the date starts, pull out its characters, and convert them into a real date. For a date like 2024-03-15 buried in a note, one formula does all three: =VALUE(MID(A2,SEARCH("20??-??-??",A2),10)). This guide shows that formula and the variations you need for file names, day/month/year dates and month names. It's for anyone cleaning exported notes, file lists or system logs, and for job-test takers who meet messy text.

How date extraction works in three steps#

Every method on this page follows the same three steps:

1. Find the position where the date starts, with SEARCH or a delimiter.
2. Pull out the date characters, with MID, TEXTAFTER or TEXTBEFORE.
3. Convert the text into a real date, with VALUE, DATEVALUE or DATE.

Step 3 matters. "2024-03-15" pulled out of text is still text: you can't sort it by date or subtract it from another date. Excel stores real dates as serial numbers, counting days from 1 January 1900, so 15 March 2024 is 45366. The guide to converting text to dates explains this in more detail.

Here are the examples used below:

Extract a yyyy-mm-dd date from anywhere in the text#

SEARCH finds text inside text, and it accepts wildcards: ? matches any one character. So "20??-??-??" matches any date from the years 2000 to 2099 in yyyy-mm-dd form:

=VALUE(MID(A2,SEARCH("20??-??-??",A2),10))

Read it from the inside out:

1. SEARCH("20??-??-??",A2) returns 9, the position where the date starts.
2. MID(A2,9,10) returns 10 characters from there: "2024-03-15".
3. VALUE(...) converts that text into the date serial number 45366.

Format the cell as a date (Windows: Ctrl+1; Mac: ⌘1) and it shows 15/03/2024 or 3/15/2024, depending on your settings. The year-month-day order is read the same way everywhere, which makes it the safest format to convert. The SEARCH function guide, the MID function guide and the VALUE function guide cover each part.

To show the date as text in a fixed style instead, wrap it in TEXT: =TEXT(VALUE(MID(A2,SEARCH("20??-??-??",A2),10)),"d mmm yyyy") returns 15 Mar 2024.

Extract a date between two characters with TEXTAFTER and TEXTBEFORE#

When the date sits between known characters, such as an underscore and a dot in a file name, TEXTAFTER and TEXTBEFORE are easier to read:

=DATEVALUE(TEXTBEFORE(TEXTAFTER(A3,"_"),"."))

1. TEXTAFTER(A3,"_") returns everything after the underscore: "2024-11-02.xlsx".
2. TEXTBEFORE(...,".") keeps everything before the dot: "2024-11-02".
3. DATEVALUE(...) converts it to 45598, which is 2 November 2024.

TEXTAFTER and TEXTBEFORE need Microsoft 365 or Excel 2024. In older versions, use the SEARCH and MID method above. The guide to extracting text between two characters shows more patterns, and the same idea works for extracting the domain from an email address.

Extract a dd/mm/yyyy date safely with DATE#

Day/month/year text is risky to convert with DATEVALUE. Excel reads it using your computer's regional settings, so 03/04/2024 could become 3 April or 4 March. Build the date yourself with DATE(year, month, day) instead. For "Shipped 15/03/2024" in A4, the first slash is at position 11:

=DATE(MID(A4,SEARCH("/",A4)+4,4),MID(A4,SEARCH("/",A4)+1,2),MID(A4,SEARCH("/",A4)-2,2))

DATE turns these into 45366, 15 March 2024, on any computer. DATE accepts number text like "03", so you don't need VALUE around each part. This assumes two-digit days and months; for dates like 5/3/2024, split the date text with TEXTSPLIT on "/" first. The lesson on how Excel stores dates explains DATE and serial numbers.

Extract a date with a month name#

Dates like "15 Mar 2024" are easier: DATEVALUE understands month names in your Excel language. For "Due 15 Mar 2024" in A5:

=DATEVALUE(TEXTAFTER(A5,"Due "))

TEXTAFTER returns "15 Mar 2024", and DATEVALUE converts it to 45366. In older Excel, use =DATEVALUE(MID(A5,5,11)) when the date always starts at the same position.

Quick fixes without formulas#

For a one-off clean-up, two built-in tools can be faster:

• Flash Fill. Type the first date by hand in the next column, then press Ctrl+E (Mac: ⌘E). Excel copies the pattern down. The results are fixed values that don't update.
• Text to Columns. Under Data > Text to Columns, split on a space or a symbol, and set the date column's format to Date with the right order (DMY, MDY or YMD).

Use a formula when new rows keep arriving, so every new note is handled the same way. A formula also shows exactly how each date was found, which makes the result easy to check.

Common problems and fixes#

• The result is a number like 45366. That's a real date. Apply a date format. See why Excel dates are numbers.
• #VALUE! from SEARCH. The text has no matching date. Wrap the formula in IFERROR: =IFERROR(VALUE(MID(A2,SEARCH("20??-??-??",A2),10)),"No date").
• Day and month swapped. DATEVALUE used your regional settings. Rebuild the date with DATE(year, month, day).
• Extra spaces. Text from other systems may have double or non-breaking spaces. Clean it with TRIM and SUBSTITUTE first.
• Numbers but no date. To pull out other numbers, such as an order ID, see how to extract numbers from text. To go the other way and put a date into a sentence, see how to combine a date and text.

Practise it#

Date extraction joins three skills: splitting text, converting text to values, and understanding how Excel stores dates. Practise TEXTBEFORE and TEXTAFTER in a live spreadsheet, then practise TEXT and VALUE to convert between numbers and text. Each lesson checks your formula instantly. Then try the exercise on this page to pull the payment date out of a note.