convert text to date

[samuel]

I have a text column 02/21/08 2315

I want to convert this to an actual date/time column keeping in military
format.

 

[Niek Otten]

=DATEVALUE(LEFT(A1,FIND(" ",A1)))+TIMEVALUE(LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),2)&":"&RIGHT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),2))

Format Custom as mm/dd/yy hhmm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a text column 02/21/08 2315
|
| I want to convert this to an actual date/time column keeping in military
| format.

 

[Rick Rothstein]

Put this formula in an unused column starting in the same row as your
topmost "text date"...

=--(LEFT(A1,11)&":"&RIGHT(A1,2))

adjusting the two A1 references to your topmost "text date" cell address,
then copy the formula down. While those cells are still selected, press
Ctrl+C, then click in your topmost "text date" cell and click
Edit/PasteSpecial from Excel's menu bar, select the Value option from the
Paste section and click the OK button. Press Esc to clear the copy
selection, select the row you just pasted over (the one that had your "text
dates" in it originally), and Custom Format the Cells with this...

mm/dd/yy hhmm

You will now have real dates that are formatted to look like the original
"text dates".