How to Convert Number String to Date and Time in Excel?


Sometimes in Excel, you may have observed that the date and time are represented as strings without any separators. This could create a huge problem if we perform any operations on the data and analyse them. If we try to do this conversion manually, then it will be a time‑consuming process. However, you can speed up this process by using the tricks mentioned in this tutorial. Read this tutorial to learn how you can convert a number string to a date and time in Excel.

Converting a Number String to Date and Time in Excel

Here we will use the format option and then change the type to complete the task. Let's go over a simple procedure for converting strings to date and time in Excel using the format cell’s function.

Step 1

Let us consider an Excel sheet where the data in the sheet is similar to the data shown in the below image.

Now to convert to date and time, select the data, then right-click and select format cells.

Select data > Right click > Format cells

Step 2

Then, in the pop-up window, select "custom" and enter the type as mm/dd/yyyy hh:mm, then click "OK" to finish our procedure.

Custom > Type > OK

Note − If we need to convert our string to only the date format, use the same process, but only change if we need to use type mm/dd/yyyy.

If we need to convert our string to only the time format, use the same process, but only change if we need to use type hh:mm:ss.

Conclusion

In this tutorial, we used a simple example to demonstrate how we can convert a number string to date and time in Excel. If our sting also includes the seconds, we use the same process but need to change the type to mm/dd/yyyy hh:mm:ss.

Updated on: 06-Mar-2023

203 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements