Converting Text Dates to Real Dates

Written by Allen Wyatt (last updated July 19, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365


4

Dennis has a worksheet that contains data that apparently originated on a non-Excel system. One column has the date shown as "TUE 08 JUL 25" (in text format). Dennis needs to convert this to an actual Excel date, but DATEVALUE gives him an error when he tries using it.

The thing that is tripping up DATEVALUE in this date format is the inclusion of the day of the week at the beginning of the string. Assuming your dates always follow this same format—3 letters for day of week, 2 for day, 3 for month, and 2 for year—then you can use this formula:

=DATEVALUE(MID(A1,5,9))

This ignores the first four characters, meaning the day of the week. If you think that the date may be longer, perhaps including a four-digit year, then the following will work just fine:

=DATEVALUE(MID(A1,5,99))

It is interesting that you could skip the use of DATEVALUE altogether. The following formula works, as well:

=MID(A1,5,99)*1

By multiplying the partial text string by 1, Excel automatically does a conversion on the date and yields the same result as DATEVALUE.

If you want to make sure that the year is always in the current century, then you can expand your formula to make the two-digit century into a four-digit century:

=DATEVALUE(MID(A5,5,7) & "20" & RIGHT(A5,2))

Regardless of the formula you use, you will need to format the result as a date. If you want to match the original format, then you can use the following as a custom format in Excel:

ddd dd mmm yy

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13310) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Understanding Phantom Macros

When you delete all the macros in a workbook, Excel may still think you have some there. Here's why that happens and what ...

Discover More

Using Stored Views

After creating different views of your worksheet data, you can display those views by simply selecting which one you want ...

Discover More

Out of Memory Errors when Accessing the VBA Editor

It can be frustrating when you get error messages doing something that you previously did with no errors. If you get an ...

Discover More

Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!

More ExcelTips (ribbon)

Calculating Future Workdays

Need to calculate the date that is a certain number of workdays in the future? You can do so using a couple of different ...

Discover More

Calculating an Age On a Given Date

Start putting dates in a worksheet (especially birthdates), and sooner or later you will need to calculate an age based ...

Discover More

Month for the Nth Sunday

Doing math with dates is quite easy in Excel. As this tip illustrates, this fact makes it easy to figure out the Nth ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is one more than 8?

2025-07-25 12:08:30

J. Woolley

Here is yet another way to isolate Dennis' dates.
1. Select the cells containing text dates like "TUE 08 JUL 25"
2. Pick Data > Data Tools > Text to Columns (Alt+A+E)
3. In the Text to Columns Wizard:
3.a. Pick Fixed Width, then click Next
3.b. Drag the dividers on both sides of month to the right beyond year
3.c. Click Next
3.d. Click the 1st column (day name) and pick "Do not import column"
3.e. Click the 2nd column (date) and pick "Date: DMY"
3.f. Confirm "Destination:"
3.g. Click Finish


2025-07-21 10:52:22

J. Woolley

Re. my most recent comment below, "\d+\D+\d+" is a simpler regular expression to isolate Dennis' dates. It is more versatile because it also works with dates like "TUE-08-JUL-25" or "TUE08JUL25" etc. On the other hand,
    =DATEVALUE("08JUL25")
returns the expected date, but
    =DATEVALUE2("08JUL25")
does not.


2025-07-20 12:01:55

J. Woolley

The regular expression "\d+\s+\D+\s+\d+" will isolate Dennis' dates. Therefore, he can use this formula with Excel 365:
    =DATEVALUE(REGEXEXTRACT(A1, "\d+\s+\D+\s+\d+"))
Or he can use this formula with My Excel Toolbox:
    =DATEVALUE2(RegExMatch(A1, "\d+\s+\D+\s+\d+"))
See https://sites.google.com/view/MyExcelToolbox/


2025-07-19 10:37:33

J. Woolley

All of the Tip's formulas assume Dennis' dates begin with 3 characters for the weekday followed by 1 space, so the date begins at the 5th character:
    MID(A1, 5, 99)
This does not account for the possibility of extra space characters before or after the weekday, nor does it allow a weekday with more or less than 3 characters.
This might be a better way to isolate the date:
    MID(TRIM(A1), FIND(" ", TRIM(A1)) + 1, 99)
Notice Excel's TRIM function removes leading and trailing space characters plus any extra spaces between words.
Re. the century problem, see DATEVALUE2 described in my recent comments here: https://excelribbon.tips.net/T012839
By the way, DATEVALUE2 recognizes more text dates than DATEVALUE. For example DATEVALUE2("JUL  08  25") returns July 8, 2025 as a date serial number, but DATEVALUE requires a comma after the day number.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.