Text to date

[Pimamedic]

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

 

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

 

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

 

[Mike H]

hi,

And Another

select the date(s) in a column then

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

Finish

Mike

 

[Pimamedic]

TY TY TY

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

 

[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

 

[Gord Dibben]

240 is equal to 3 hours?

At 80 minute hours maybe<g>


Gord Dibben MS Excel MVP

 

[Pimamedic]

OK if you insist LOL

240 is equal to 3 hours?

At 80 minute hours maybe<g>


Gord Dibben MS Excel MVP