Date format for general formatting

[rrupp]

I have an Excel 2003 worksheet that was sent to me that I want to manipulate.
The date column is formatted as general and reads 61908 for June 19, 2008
but I want it to read 06/19/08. I'm sure it's something simple I'm missing.
I've searched the archives but cannot find a solution. Please advise and
thanks for your time.

 

[Otto Moehrbach]

I don't know what you have. The date 19 June, 2008 will produce 39618 if
the cell is formatted as General. If you want the cell to display a date,
select the cell, then click on Format - Cells - Number tab - Date, and
choose the date display you want. Click OK. HTH Otto

 

[rrupp]

The column reads 61908 but I want it to be 06/19/08. When I format it by
going to format, cells, number tab, custom mm/dd/yyyy it reads the cell as
06/29/2069.

 

[Mike H]

Hi,

For the posted example of 61908 this works
=DATE(20&RIGHT(A1,2),LEFT(A1,1),MID(A1,2,2))

But that would fail for a 6 number date Oct - Dec and for those this would
work
=DATE(20&RIGHT(A2,2),LEFT(A2,2),MID(A2,3,2))

What I don't understand and hope someone can explain is why putting them in
an if statement doesn't work because I'm sure it should and am confused.

=IF(LEN(A1=6),DATE(20&RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2)),DATE(20&RIGHT(A1,2),LEFT(A1,1),MID(A1,2,2)))

Mike

 

[Mike H]

Hmmm

=DATE(20&RIGHT(A1,2),IF(LEN(A1)=6,LEFT(A1,2),LEFT(A1,1)),IF(LEN(A1)=6,MID(A1,3,2),MID(A1,2,2)))

Now even more confused because this does work for both

Mike

 

[Gord Dibben]

Data>Text to Columns>Next>Next>Column Data Format>Date>MDY and Finish

Format to mm/dd/yy


Gord Dibben MS Excel MVP