Converting numbers into date format...

Hello,

I have a spreadsheet which has the date set out like this: 20070101

If there any way i can change this to either 2007/01/01 or 01/01/2007?
I have tried using the date format (in the cells) but it just comes up with ######## i tried expanding the cell but no luck, this is very importand that i do this as its for a customer.

Thanks

If you have a column of such "dates" you can convert by using

Data > Text to columns > Next > Next

At step 3 select "date" and "YMD", "Finish"

You should now have valid dates that you can format any way you like

I tried daddylonglegs suggestion & it did not work for me

Using a formula try

=RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4)
or
=LEFT(A1,4)&"/"&MID(A1,5,2)&"/"&RIGHT(A1,2)

Hello mudraker,

why didn't it work? What result did you get?

For a formula approach

=TEXT(A1,"0000-00-00")+0

format as date

Thanks for your fast reply. It worked perfectly.

Just one more thing. How could i do the same, but with time format?

for example i have the time in HHMMSS e.g. 170405 and i want it to be changed to 17:04:05

However when the time is in the morning, it puts 90405 , instead of 090405

how can i work around this? so its either 09:04:05 or 9:04:05??

I hope you understand

If not i will explain more, i have tried the function above (data menu) but no luck.

Thanks

You'd have to do that with a formula in another cell. You can use TEXT function similar to above so if you have your "time" in A1 use this formula in another cell, e.g. B1

=TEXT(A1,"00\:00\:00")+0

format B1 in time format e.g. hh:mm:ss

Brilliant, that wworked perfectly.

So i take it there is no way to replace the cells with the 90405 format? with that of the new formula? if you understand me?
Can i not do the formula into a different cell, then delete the old cells?

Thanks

You firstly need to convert the new times to fixed values (rather than formulas) then you can delete the original "times".

If you have the originals in column A and new times in B. Select column B, copy and then Edit > Paste Special > values.

You can now delete column A or just paste column B on top of A

Hi Again Guys,

i am trying to convert seconds into minutes using excel.
converting it is fine, because its divided by 60. However the end result isnt what i am after. I need seconds to be converted into minutes:seconds
where as, if i put "=sum(115/60)" i get "1.91667" which would be 1.92 of an hour i assume?

I would like it to display something like: 00:01:55 (hh:mm:ss) but when i format the cell, it does not convert it properly.

Any answers?

Thanks

You need to divide by 86400 (the number of seconds in a day), e.g. if you have 115 in cell A1

=A1/86400

format as [h]:mm:ss