Convert text-based dates into proper dates in Excel

Tomorrow's date is 01/07/2023.

However, for some of you, that's a week into the new year.

𝘐𝘵'𝘴 𝘯𝘰𝘵 𝘑𝘢𝘯𝘶𝘢𝘳𝘺 7 𝘵𝘰𝘮𝘰𝘳𝘳𝘰𝘸! 🤔

With no universally accepted format, it's fair to say that dates can give us headaches. 🤯

If you download an Excel workbook from the web or you're sent one, you might find any dates present use the American format (MDY).

This isn't always immediately apparent, as a sea of dates with day numbers below 13 can easily lead you to read them the wrong way around.

It might be easy to tell that 07/31/2023 is in the American format, but 01/07/2023 definitely isn't.

What's more, these dates might also be stored as text. As actual dates are serial numbers, text-based dates can prove problematic in calculations.

If you are having issues with dates, it's first worth checking the values using the ISTEXT and/or ISNUMBER functions. The former checks whether a value is text, and the latter does the same for numbers.

To solve these issues, simply do the following:

1️⃣ Select the cells containing the dates.
2️⃣ On the Data tab, select Text to Columns.
3️⃣ Select Next twice.
4️⃣ From the Date dropdown, pick MDY.
5️⃣ Select Finish.

Suddenly, MDY becomes DMY, and the text-based dates are converted into proper ones!

#exceleration #excel #globalexcelsummit

---

The Global Excel Summit is the world's largest gathering of Microsoft Excel users and experts.

Find us on: