convert string to date and time

[wxornot]

I have the string in a cell:

20070728000000

This is suppose to represent 7/28/2007 12:00:00 AM

Given these formulas I can get the date and time.

=DATE(MID(B4,1,4),MID(B4,5,2),MID(B4,7,2))
=TIME(MID(B1,9,2),MID(B1,11,2),MID(B1,13,2))

How can I merge the above formulas into one field so date and time are together?

thanks.

 

[andiemac]

You were almost there.

=DATE(MID(B4,1,4),MID(B4,5,2),MID(B4,7,2))+TIME(MID(B4,9,2),MID(B4,11,2),MID(B4,13,2))

You will also have to format the cell to display the time as part of the date.

 

[wxornot]

I figured I was close. Such a simple command I've never ran across until now.

thanks a bunch

 

[Ron Coderre]

With
A1: 20070728123400

This regular formula returns the date/time value of that cell:
B1: =--TEXT(A1,"0000\-00\-00\ 00\:00\:00")

Format the formula cell to display Date and Time

In the above example, the formula resolves to 2007-07-28 12:34:00

Does that work for you?