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).

Cavalierly

See this link: #15918573. The logic is to format the cells and then use cell.formula = cell.Text to rewrite to it. – Bertelli

Answer 1

Have you tried the =DateValue() function?

To include time value, just add the functions together:

=DateValue(A1)+TimeValue(A1)

Handmaiden

With just datevalue I can pick out the date but not time. Timevalue picks out the time but not the date... – Cavalierly

what about =DateValue(A1)+TimeValue(A1)? – Handmaiden

What about =value(A1)? – Uninstructed

Answer 2

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.

Soberminded

The source dates are always in text datatype. And I always want to convert them to numbers in a new column. The problem seems to boil down to that there are no datetime datatype... – Cavalierly

Answer 3

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.

Troostite

Answer 4

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

Zeculon