WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to convert text to date in Excel

December 12, 2022
2.6K Views

Working with date values in Excel is frequently necessary for corporate data analysis in order to provide answers to questions like how much money did we make today? and how does this compare to the same day last week? And when Excel doesn't identify the values as dates, it might be challenging.

Regrettably, this is commonplace, especially when several users are entering this data, copying it from other systems, pasting it into databases, and so on.

In this article, we'll go over four unique situations and their associated fixes for converting text to date values and will let you know the process of how to convert text to date in excel.

Dates that contain a Period/Full stop in excel:

The use of the full stop character to separate the day, month, and year when entering dates into Excel is perhaps one of the most frequent errors beginners make.

Since Excel won't recognize this as a date value, it will be saved as text instead. However, you can use the Find and Replace tool to fix this issue. Excel will recognize the data as dates automatically if full stops are swapped out for slashes (/).

Choose the columns to execute the find and replace on.

Press Ctrl+H or choose Home > Find & Select > Replace.

Put a period (.) in the Find what field of the Find and Replace box and a slash (/) in the Replace with field. Click Replace All after that.

Excel interprets the new format as a date after converting all full stops to slashes.

Converting the yyyymmdd Format in excel:

It will necessitate a different strategy if you obtain dates in the format displayed below.

This format is fairly common in technology since it clears up any confusion regarding how various nations maintain their date values. Excel won't immediately be able to interpret it, though.Click Data > Text to Columns after choosing the value range you wish to convert.

The wizard for Text to Columns appears. In order to move on to step three, as seen in the figure below, click Next after completing steps one and two. Choose Date, then from the list choose the date format that will be used in the cells. We are dealing with a YMD format in this illustration.

Note: This above written article is an attempt to show you how to convert text to date in excel online, 2016 and 2019, in both windows and mac.You just need to have a little understanding of how and which way things work and you are good to go. With having this basic knowledge or information of how to use it, you can also access and use different other options on excel or spreadsheet. Also, it is very similar to Word or Document. So, in a way, if you learn one thing, like Excel, you can automatically learn how to use Word as well because both of them are very similar in so many ways. If you want to know more about WPS Office, you can download WPS Office to access, Word, Excel, PowerPoint for free.    

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.