Formatting date cells that are stored as a number

[sobeitjedi]

I have a range of numbers in a column that are stored as a number:

StartDate
20120103090000
20120104000000
20120104000000
20120104100000
20120104140000
20120104150000
20120104153000
20120105000000
20120105000000
20120105101500
20120105130000
20120105130000
20120105130000
20120105140000
20120105140000
20120105140000
20120105140000
20120105150000
20120105150000
20120106000000
20120106110000

How do I format this so it's in a date/time format?

 

[steve the fish]

It would be easier if you translated what they meant rather than us having to guess.

 

[sobeitjedi]

Sorry, thought it was clear

20120103090000 = 2012(year)01(month)03(day)09(hour)00(Minute)00

 

[ISY]

Hi


Cells A2 = 20120103090000

Custom Excel number format
0000\/00\/00 00\:00\:00

formulas B2 =--TEXT(LEFT(A2,8),""0000\/00\/00"")
Date number format


formulas C2 =--TEXT(--RIGHT(A2,6),"00\:00\:00")
Hour number format

 

[sobeitjedi]

Done that, but when I put the formula in B2, I get an error "There's a problem with this formula" etc (for the date formula - the time one seems to work)

The bit of the formula that'#s highlighted is: ""0000\

 

[ISY]

Hi

Different version

formulas B2 =--TEXT(LEFT(A2,8),"0000\/00\/00")
Date number format

 

[sobeitjedi]

Perfect - thank you