Want date in format mmm-yy

[vbanovice123]

Hi

I have the value as 201004 and format is General.

If I need to change the cell value to Apr-10 what should I do?

Is there a formula I can use?

Thanks

Regards,
vbanovice123

 

[julia55]

Check out this page, maybe it will help you.
http://www.excelforum.com/excel-pro...nging-order-of-text-string-inside-a-cell.html​

 

[shg]

Try this:

=--TEXT(A1, "0000\/00""/01""")

 

[Rick Rothstein]

Try this:

=--TEXT(A1, "0000\/00""/01""")

A touch shorter...

=--TEXT(A1, "0000-00-\01")

Also, I like using dashes rather than slashes when the order is yyyy mm dd... I am thinking this might be a preferred internation symbol for that date ordering (in any case, it helps makes things more compact).

 

[vbanovice123]

Thanks. But I want the format as Apr-10 (mmm-yy)

 

[Rick Rothstein]

Thanks. But I want the format as Apr-10 (mmm-yy)

Once you employ either "shg" or my formulas, you will have a real Excel date (in serial number form) which you can then use Format Cells to format the cell as a Date (or Custom) in order to display the value anyway you want (formulas cannot format cells, so you have to do that step manually).

 

[MikeWx]

This is a bit longer, but I think it gives you what you are looking for:

=TEXT(DATE(LEFT(A1,4),RIGHT(A1,2),1),"mmm-yy")

Mike

 

[Rick Rothstein]

This is a bit longer, but I think it gives you what you are looking for:

=TEXT(DATE(LEFT(A1,4),RIGHT(A1,2),1),"mmm-yy")

That, of course, is returning a text value and not a date, which may be sufficient for the OP's needs. If so, here is a shorter formula (7 characters and 2 function calls less) than the one you posted...

=TEXT(TEXT(A1, "0000-00-\01"),"mmm-yy")

 

[T. Valko]

A touch shorter...

=--TEXT(A1, "0000-00-\01")

Also, I like using dashes rather than slashes when the order is yyyy mm dd... I am thinking this might be a preferred internation symbol for that date ordering (in any case, it helps makes things more compact).

Nice one, Rick!

 

[Ron Coderre]

A touch shorter...

=--TEXT(A1, "0000-00-\01")

Also, I like using dashes rather than slashes when the order is yyyy mm dd... I am thinking this might be a preferred internation symbol for that date ordering (in any case, it helps makes things more compact).

How about going just a little bit shorter (without getting too arcane):

=--TEXT(A1,"0000-00-1")