Date format

[Pierre]

How would we get 2008-01 to read as "Jan-08"?, etc for months and
years.

I'd use a helper column if necessary. Custom format is better though.

TIA.

Pierre

 

[Sam Wilson]

Type the following in to the custom format:

mmm-yy

 

[Pierre]

Type the following in to the custom format:

mmm-yy

Sam, thanks for the reply. Couldn't get it to work though.

Pierre

 

[Pete_UK]

It looks like 2008-01 is taken as text by Excel, so changing the
format of the cell will not work - instead, you will need a formula in
a helper cell, such as:

=DATE(LEFT(A1,4),RIGHT(A1,2),1)

and then format this cell using the custom format of mmm-yy. Copy the
formula down if you have many such entries.

Hope this helps.

Pete

 

[Pierre]

It looks like 2008-01 is taken as text by Excel, so changing the
format of the cell will not work - instead, you will need a formula in
a helper cell, such as:

=DATE(LEFT(A1,4),RIGHT(A1,2),1)

and then format this cell using the custom format of mmm-yy. Copy the
formula down if you have many such entries.

Hope this helps.

Pete

Pete: it worked like a charm. Thank you.

Pierre

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

It looks like 2008-01 is taken as text by Excel, so changing the
format of the cell will not work - instead, you will need a formula in
a helper cell, such as:

=DATE(LEFT(A1,4),RIGHT(A1,2),1)

and then format this cell using the custom format of mmm-yy. Copy the
formula down if you have many such entries.

Hope this helps.

Pete

Pete: it worked like a charm. Thank you.

Pierre

 

[Gord Dibben]

Data>Text to Columns>Next>Next>Column Data Format>Date>YMD>Finish.


Gord Dibben MS Excel MVP