How to convert text to date in Excel
A free Office suite fully compatible with Microsoft Office
A free Office suite fully compatible with Microsoft Office
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.
Was this helpful?
Yes
No
Trending Tutorials
- 1. How to get month name from a date in Excel (3 easy ways)
- 2. Check if value is in list in Excel (3 easy methods)
- 3. How to Copy File Names in Excel from a Folder?
- 4. How to color cell based on value in Excel?
- 5. How to compare two excel sheets and highlight differences
- 6. How to add text to beginning or end of all cells in Excel