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

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.

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

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

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

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

Examples related to excel

Examples related to date-format

Examples related to worksheet-function