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
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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
Don't want to use the EOMONTH function to figure out the end of a given month? Here are some other ideas for discovering ...
Discover MoreBecause Excel stores dates internally as serial numbers, it makes doing math with those dates rather easy. Even so, it ...
Discover MoreWant to find out how many of a particular weekday occurs within a given month? Here's how you can find the desired ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments