Converting numbers to dates

W

Winger

Hi there, I have a spreadsheet that is a download from another program.
It has exported the dates in military time with no ":" ie 3:00PM came
through as 1500. Through left and right statements I can make it look
like 15:00 in a new column and then copy and paste special-values so
that the cell is no longer a formula, it is 15:00. Then I format the
cell as time, but stays as text. If I hit F2 and enter it converts to
time. Unfortunetly the spreadsheet is over 2000 lines. Can anyone
help me with this?
 
E

Earl Kiosterud

Winger,

you could make a temporary helper column, with
=TIME(LEFT(A2,2),RIGHT(A2,2),0). To change permanently, copy, then
paste-special - values.
 
R

Ron Rosenfeld

Hi there, I have a spreadsheet that is a download from another program.
It has exported the dates in military time with no ":" ie 3:00PM came
through as 1500. Through left and right statements I can make it look
like 15:00 in a new column and then copy and paste special-values so
that the cell is no longer a formula, it is 15:00. Then I format the
cell as time, but stays as text. If I hit F2 and enter it converts to
time. Unfortunetly the spreadsheet is over 2000 lines. Can anyone
help me with this?
Click to expand...


With your military time in A1:

=--TEXT(A1,"00\:00")

will result in an Excel time value. Format the result as Time.

HTH.


--ron
 
Top