Convert Text to Date in Excel: Quick Guide to Formatting

Manipulating data in Excel is an everyday task for many of us and dealing with dates can sometimes be a bit tricky. I’ve stumbled across the issue where dates entered as text don’t play well with calculations or sorting. You know the hassle—it’s when Excel sees your entry as plain text and not an actual date which can mess up any analysis you’re trying to perform.

I’ve personally battled these inconsistencies, especially when I receive spreadsheets from different people who enter dates in various formats. It can be a real headache but thankfully, I found several methods to whip those text dates into shape. It’s all about using the right Excel functions to transform text into a format that Excel recognizes as a date. I’m excited to share these techniques with you and I’m confident that they’ll save you time and frustration. Let’s jump into the nitty-gritty of converting text to dates!

1. Convert a Text to a Date

In Excel, it’s not uncommon to encounter dates formatted as text strings. To convert these into actual date values that Excel can recognize for calculations and sorting, there are several methods at our disposal.

Using DATEVALUE Function:

• When your text dates are in recognizable date formats, the DATEVALUE function comes in handy.
• It’s as simple as =DATEVALUE(A1), where A1 contains your text date.

But sometimes, dates in a text format don’t play nice, especially if they follow non-standard formatting. That’s where a combination of text functions such as LEFT, MID, and RIGHT becomes useful to construct a date value manually.

Here’s a neat trick using text functions:

=DATE(LEFT(A1, 4), MID(A1, 5, 2), RIGHT(A1, 2))

This formula assumes that your date string is in the format YYYMMDD in cell A1.

The Text to Columns Wizard:

• I often use the Text to Columns option when I need to split date components spread across a single column.

Convert XX to 19XX or 20XX:

• Ever had those pesky two-digit years? Excel’s built-in date format options and some creative use of formulas can help interpret them as 1900s or 2000s dates appropriately.

To avoid #VALUE! errors and ease your date conversion process, remember to always check the number format of your cells. Sometimes a simple switch from ‘Text’ to ‘Date’ format via the Format Cells dialog box does the trick.

Lastly, don’t overlook the power of Paste Special for date operations. It’s like a Swiss Army knife for date conversions. For example, adding zero to a date value using Paste Special can force Excel to re-evaluate it as a date.

Remember, Excel treats dates as serial numbers, so getting your dates from text to serial number is the real game-changer in making sure your date-related calculations stay on point.

2. Date with Back Slashes

When I’m working with Excel and see dates with backslashes, they’re just text to the system. I use a nifty formula to get around this and convert them to actual date format:

=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2))

Here’s what each part does:

• RIGHT(A2,4): Grabs the year from the text.
• MID(A2,4,2): Fetches the month.
• LEFT(A2,2): Gets the day from the start.

After applying this, Excel understands it’s a date!

3. Date with Month Name

When I run into a cell where the month is spelled out (like “March 5, 2024”), Excel doesn’t recognize it as a date. No worries, though—I use a simple formula to fix this:

=DATEVALUE(RIGHT(myCell,2) & "-" & TEXT(MID(myCell,6,3),"MMM") & "-" & LEFT(myCell,4))

Here’s what this does:

• RIGHT(myCell,2) grabs the year
• MID(myCell,6,3) fetches the month name
• LEFT(myCell,4) picks out the day

Converting “March 5, 2024” becomes a breeze, and Excel can then understand and work with the date flawlessly!

4. Date with Dots

If I run across dates formatted with dots—like 12.05.2023—I know they won’t fly in Excel by default. Here’s a quick fix I use to get them recognized:

=DATE(RIGHT(A1,4), MID(A1,4,2), LEFT(A1,2))

Check out this snapshot showing it in action:

And here’s the actual formula breakdown:

• Year: Grab the rightmost four digits.
• Month: Snag the middle pair of numbers.
• Day: Pick the first two numbers from the left.

Nice and simple, right? Pop this into a cell, and I’ve got a date Excel will cozy up to.

5. Date with the Month Name and a Comma

=DATEVALUE(LEFT(A5,2)&"-"&TEXT(MID(A5,4,3),"MMM")&"-"&RIGHT(A5,4))

• Custom Date Format: Includes full month name and a comma.
• Ensures dates appear in a recognizable format for Excel.
• Suitable when dates include the long-form month.

Transform text to the Date type effortlessly with this formula.

6. Date with the Day Name

To handle dates with day names stored as text, I use a specific Excel formula to convert them to a recognizable date format. For example:

• Formula: =DATEVALUE(MID(A6,FIND(",",A6)+6,2) & "-" & MID(A6,FIND(",",A6)+2,3) & "-" & RIGHT(A6,4))
• Result: Converts text date with day name into a standard date format

Here’s a visual for clarity:

7. Date with a Day Name in the End

I’ve noticed dates with the day name tacked on at the end can trip up Excel. Especially when there’s a comma splitting up the date and the day. Check this out:

To make it work, I use this nifty formula:

Formula:

=DATEVALUE(MID(A7,10,2) & "-" & MID(A7,6,3) & "-" & LEFT(A7,4))

This way, Excel gets the point and treats it like the date it is.

8. Date Having a Suffix with Day

I tackled dates suffixed with “th” making them text. Excel can’t recognize these, but here’s a formula I found handy:

=DATEVALUE(LEFT(A8,FIND("th",A8)-1) & "-" & MID(A8,FIND(" ",A8)+1,3) & "-" & RIGHT(A8,4))

The formula strips the “th” and formats it to a recognizable date.

9. Date with Space Between Day, Month, and Year

When I encounter a date formatted with spaces, like “12 Feb 2024”, I can make Excel recognize it properly. Here’s the formula I use:

=DATE(RIGHT(A1,4), MID(A1,4,2), LEFT(A1,2))

• LEFT(A1,2) grabs the day.
• MID(A1,4,2) extracts the month.
• RIGHT(A1,4) gets the year.

With this, Excel turns that text into a recognizable date format.

10. Date with Short Month Name

When I encounter a date starting with a short month name, like “Apr” for April, I whip up this nifty formula:

=DATEVALUE(MID(A10,5,2) & "-" & LEFT(A10,3) & "-" & RIGHT(A10,4))

This rearranges the parts of the date into a format Excel recognizes.

11. Date Without Any Space Between Day, Month, and Year

If I’m working with dates clumped together without spaces, like “15022023” for the 15th of February, 2023, I just use a specific Excel formula to parse it into a recognizable date format. Here’s how to format this properly:

=DATE(RIGHT(A1,4), MID(A1,3,2), LEFT(A1,2))

By applying this, Excel knows that:

• LEFT(A1,2) is the day (15)
• MID(A1,3,2) is the month (02)
• RIGHT(A1,4) is the year (2023)