Converting a string to a date in a cell

Question

I have 100 000 rows of data in Excel  Some of the fields are dates  but the fields in Excel are as text  I need these fields in number format including both dates AND time  e g  21 10 2011 13 10 50   Formatting the cells doesn t work because that doesn t change the datatype  I can pick out the date and time with formulas but not get them in the same cell   So what I am looking for is the formula to calculate the number representation of a date  the one you see if you format a date as a number

User · Answer

I was struggling with this for some time and after some help on a post I was able to come up with this formula =(DATEVALUE(LEFT(XX,10)))+(TIMEVALUE(MID(XX,12,5))) where XX is the cell in reference.

I've come across many other forums with people asking the same thing and this, to me, seems to be the simplest answer. What this will do is return text that is copied in from this format 2014/11/20 11:53 EST and turn it in to a Date/Time format so it can be sorted oldest to newest. It works with short date/long date and if you want the time just format the cell to display time and it will show. Hope this helps anyone who goes searching around like I did.

User · Answer

Have you tried the  DateValue   function   To include time value  just add the functions together    DateValue A1  TimeValue A1

User · Answer

The best solution is using DATE   function and extracting yy  mm  and dd from the string with RIGHT    MID   and LEFT   functions  the final will be some DATE LEFT   MID   RIGHT     details here

User · Answer

To accomodate both data scenarios you have  you will want to use this   datevalue text a2  mm dd yyyy      That will give you the date number representation for a cell that Excel has in date  or in text datatype

[excel] Converting a string to a date in a cell

Examples related to excel

Examples related to date-format

Examples related to worksheet-function