Excel Cell Formatting - change 231104 to 23/11/04

[mrmc74]

Hello,

Could somebody please explain how to do this?

Thank you.

 

[xlbo]

not so much formatting as a complete rejig of the data in the cell

1st you must understand that dates / times are simply numbers FORMATTED to look like a date
The Number associated with a date is the number of days that date is from 1/1/1900

Excel can interpret many things as dates but in order to do so, it must either LOOK like a date or be able to be formatted as a date. Thus, you must split your number up to make it LOOK like date. Excel can then evaluate that and convert it to a true date:

To split the number:
=left(A1,2)&"/"&mid(A1,3,2)&"/"&right(A1,2)

To evaluate that split:
=VALUE(left(A1,2)&"/"&mid(A1,3,2)&"/"&right(A1,2))

Once you format that as dd/mm/yy, you will get the result you want. This all assumes that your 231104 is in cell A1

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[mrmc74]

Geoff,

Thanks a lot for solving the problem.

Had an issue when the dd had a leading 0 i.e. 01.

Modified the number split to reflect this.

=LEFT(A1,1)&"/"&MID(A1,2,2)&"/"&RIGHT(A1,2)

Regards,

Mark