Date format for general formatting
 

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.

Date format for general formatting
 

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" wrote in message
...
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.

Date format for general formatting
 

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.


"Otto Moehrbach" wrote:

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" wrote in message
...
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.

Date format for general formatting
 

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(A 1,3,2)),DATE(20&RIGHT(A1,2),LEFT(A1,1),MID(A1,2,2) ))

Mike


"rrupp" wrote:

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.


"Otto Moehrbach" wrote:

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" wrote in message
...
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.

Date format for general formatting
 

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

"Mike H" wrote:

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(A 1,3,2)),DATE(20&RIGHT(A1,2),LEFT(A1,1),MID(A1,2,2) ))

Mike


"rrupp" wrote:

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.


"Otto Moehrbach" wrote:

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" wrote in message
...
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.

Date format for general formatting
 

DataText to ColumnsNextNextColumn Data FormatDateMDY and Finish

Format to mm/dd/yy


Gord Dibben MS Excel MVP

On Tue, 15 Jul 2008 11:06:01 -0700, rrupp
wrote:

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.