Text to date

P

Pimamedic

How do I convert 20090228 to read as 02/28/2009 is there a format to do
this?? TY
 
L

Luke M

No, but you can convert it with a formula.

=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

Format as desired.
 
D

Dave Peterson

One cell?
You could use a formula:
=--text(a1,"0000\/00\/00")
and give that cell a nice date format.

If you had a column of these cells to convert...
Select the column
Data|text to columns (xl2003 menus)
fixed width (remove any lines that excel guessed)
choose date ymd as the formatt
and finish up

And give the selected range a nice date format.
 
M

Mike H

hi,

And Another

select the date(s) in a column then

Data|Text to columns - Next - Next - Select Date - YMD

Finish

Mike
 
P

Pimamedic

TY TY TY

Now how do I convert 240 to time format which in this case woudl be 3 hours
 
D

Dave Peterson

If it were 4 hours (60 minutes times 4), you could put 1440 in an empty cell
(1440 = number of minutes in a day (24*60))
copy that cell
select the range to fix
Edit|paste special|choose divide and values

And then format the cell with a nice time format like: hh:mm:ss
 
Top