Convert text date/time to a real date time in excel

excel date time data-conversion
user3085606 picture user3085606 · Mar 28, 2016 · Viewed 37.8k times · Source

My data is extracted from an application and it has a text that looks like a date/time in excel. How do I actually convert "3/24/2016 11:22:07 PM" (in text) to a real date/time conversion? I've tried formatting the cells but it doesn't work.

Answer

PaulG picture PaulG · Mar 28, 2016

For a date conversion: 

=DATEVALUE(TEXT(A1,"MM/DD/YYYY"))

For a time conversion:

=TIMEVALUE(TEXT(A1,"HH:MM:SS"))

For datetime conversion:

=DATEVALUE(TEXT(A1,"MM/DD/YYYY"))+TIMEVALUE(TEXT(A1,"HH:MM:SS"))

Where A1 has the data you wish to convert.

By the way, then you may wish to format the cell to a date/time or whatever.

Hope that helps.

Related questions

Count number of times a date occurs and make a graph out of it

I have a list of dates, each date in it can occur more than once. I want to count the number of times each date occurs (histogram) and display it in a graph (with the Y axis being the number …

Read More
Formula to convert date to number

I would like to know the formula to convert a date in 10/26/2013 to 41573 number as done in Excel. Like how 10/26/2013 is converted to 41573.

Read More
Ordering issue with date values when creating pivot tables

I have a 2-column spreadsheet which contains Dates and Sales figures as follows: When doing Right click -> Format Cells the values in the Dates column properly appear as Date (in the M/D/YYYY format). This applies to …

Read More