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.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Excel makes it easy to import information created in other programs. Converting the imported data into something you can ...
Discover MoreExcel provides quite a bit of flexibility in how you can format dates. Even so, some dates simply cannot be formatted ...
Discover MoreNeed to figure out if a given year is a leap year? It's not as easy as you think! This tip provides a plethora of ways ...
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