
Step-by-Step Guide: Transforming Text Into Dates Within Microsoft Excel

Step-by-Step Guide: Transforming Text Into Dates Within Microsoft Excel
Quick Links
Analysis of business data often requires working with date values in Excel to answer questions such as “how much money did we make today” or “how does this compare to the same day last week?” And that can be hard when Excel doesn’t recognize the values as dates.
Unfortunately, this is not unusual, especially when multiple users are typing this information, copying and pasting from other systems and importing from databases.
In this article, we will describe four different scenarios and the solutions to convert the text to date values.
Dates that Contain a Full Stop/Period
Probably one of the most common mistakes beginners make when typing dates into Excel is doing so with the full stop character to separate the day, month, and year.
Excel will not recognize this as a date value and will go ahead and store it as text. However, you can solve this problem with the Find and Replace tool. By replacing the full stops with slashes (/), Excel will automatically identify the values as dates.
Select the columns on which you want to perform the find and replace.
Click Home > Find & Select > Replace—or press Ctrl+H.
In the Find and Replace window, type a full stop (.) in the “Find what” field and a slash (/) in the “Replace with” field. Then, click “Replace All.”
All full stops are converted to slashes and Excel recognizes the new format as a date.
If your spreadsheet data is regularly changing, and you want an automated solution for this scenario, you could use the SUBSTITUTE function .
=VALUE(SUBSTITUTE(A2,”.”,”/“))
The SUBSTITUTE function is a text function, so cannot convert it to a date on its own. The VALUE function will convert the text value to a numeric value.
The results are shown below. The value needs to be formatted as a date.
You can do this using the “Number Format” list on the “Home” tab.
The example here of a full stop delimiter is typical. But you can use the same technique to replace or substitute any delimiter character.
Converting the yyyymmdd Format
If you receive dates in the format shown below, it will require a different approach.
This format is quite standard in technology as it removes any ambiguity about how different countries store their date values. However, Excel will not initially understand it.
For a quick manual solution, you could use Text to Columns .
Select the range of values you need to convert and then click Data > Text to Columns.
The Text to Columns wizard appears. Click “Next” on the first two steps so that you are at step three, as shown in the image below. Choose Date and then select the date format being used in the cells from the list. In this example, we are dealing with a YMD format.
If you would like a formula solution, then you could use the Date function to construct the date.
This would be used alongside the text functions Left, Mid and Right to extract the three parts of a date (day, month, year) from the cell contents.
The formula below shows this formula using our sample data.
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
Using either of these techniques, you can convert any eight-digit number value. For example, you might receive the date in a ddmmyyyy format or a mmddyyyy format.
DATEVALUE and VALUE Functions
Sometimes the problem is not caused by a delimiter character but has an awkward date structure simply because it is stored as text.
Below is a list of dates in a variety of structures, but they are all recognizable to us as a date. Unfortunately, they have been stored as text and need converting.
For these scenarios, it is easy to convert using a variety of techniques.
For this article, I wanted to mention two functions to handle these scenarios. They are DATEVALUE and VALUE.
The DATEVALUE function will convert text into a date value (probably saw that coming), while the VALUE function will convert text into a generic number value. The differences between them are minimal.
In the image above, one of the values contains time information as well. And that will be a demonstration of the functions’ minor differences.
The DATEVALUE formula below would convert each one to a date value.
=DATEVALUE(A2)
Notice how the time was removed from the result in row 4. This formula strictly returns just the date value. The result will still need to be formatted as a date.
The following formula uses the VALUE function.
=VALUE(A2)
This formula will produce the same results except for in row 4, where the time value is also maintained.
The results can then be formatted as date and time, or as a date to hide the time value (but not remove).
Also read:
- [New] In 2024, State-of-the-Art Methods for Documenting Digital Education
- [New] Jest Juggler Mummified Memers for 2024
- [SOLVED] Resident Evil Village Mouse Issues
- [Updated] In 2024, How to Edit Photos on Snapchat
- [Updated] Utilizing Zoom to Upgrade TikTok Video Aesthetics
- Bypassing and Resolving PC Instability with NBA 2K21
- Chrome Browser Issues: How to Load Stuck Pages Effectively
- Direct Access: Linux, Forget WSL
- Embracing Linguistic Diversity with Windows Fonts
- Guide to Fix Download/Install Problems for Age of Empires IV on Microsoft's Platform
- Leading Laptop and Desktop Monitor Choices for 2Er4 Tech Enthusiasts
- Overcoming Crashes in Chivalry 2 with These Proven Tips & Strategies
- Resolving Missing Audio in Discord Screen Shares - Step-by-Step Solutions
- Silent Sync: A User's Manual for Pairing AirPods to Quest 3 Headphones Discreetly | DigitalLife
- Step-by-Step Troubleshooting for High FPS on Splitgate
- Top 4 Ways to Trace Oppo Find N3 Location | Dr.fone
- Winning the Showdown with Confidence: Your Ultimate Guide to Correcting TFLA0002
- Title: Step-by-Step Guide: Transforming Text Into Dates Within Microsoft Excel
- Author: Daniel
- Created at : 2024-12-03 18:20:04
- Updated at : 2024-12-06 18:35:24
- Link: https://win-answers.techidaily.com/step-by-step-guide-transforming-text-into-dates-within-microsoft-excel/
- License: This work is licensed under CC BY-NC-SA 4.0.