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.
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!
Need to figure out when a fiscal year ends when that period does not correspond to the calendar year? Here are some ways ...
Discover MoreWant to add an ordinal suffix to a number, as in 2nd, 3rd, or 4th? Excel doesn't provide a way to do it automatically, ...
Discover MoreWant to know which day of the month is the first business day? The easiest way to determine the date is to use the ...
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