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

 

[T. Valko]

How about going just a little bit shorter (without getting too arcane):
=--TEXT(A1,"0000-00-1")

I thought I had tried that and got an error but I was mistaken.

 

[Rick Rothstein]

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

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

I am surprised that works as it does not follow the "international date" standard (which would require 2-digit months and days). See 2nd bulleted point under "General principles" here...

http://en.wikipedia.org/wiki/ISO_8601

I guess this must be a case of Excel being helpful.

 

[pgc01]

And another one, just for fun:

=--REPLACE(A1,5,0,"-")

 

[Ron Coderre]

I am surprised that works as it does not follow the "international date" standard (which would require 2-digit months and days). See 2nd bulleted point under "General principles" here...

http://en.wikipedia.org/wiki/ISO_8601

I guess this must be a case of Excel being helpful.

Practically nothing surprises me with Excel any more

 

[Ron Coderre]

And another one, just for fun:

=--REPLACE(A1,5,0,"-")

Can you get that to work? I only get #VALUE! errors.
I had to change it to this: =--REPLACE(A1&"-1",5,0,"-")

 

[Marcelo Branco]

Or
=TEXT(A1,"0000-00-1")+0

M.

-Edit-
I hadnt seen Ron's formula
identical...

 

[pgc01]

Can you get that to work? I only get #VALUE! errors.
I had to change it to this: =--REPLACE(A1&"-1",5,0,"-")

Yes, it works. I'm using excel 2000.

Your formula returns the date in the usual international format:

YYYY-MM-DD

The ISO 8601, however, allows for 2 other formats

YYYY-MM, the one I used

and

YYYYMMDD

I'll try tomorrow in excel 2010.

 

[pgc01]

... and also works in vba, I tried in the Immediate Window:

?CDate("2010-02")

 

[T. Valko]

Yes, it works. I'm using excel 2000.

Your formula returns the date in the usual international format:

YYYY-MM-DD

The ISO 8601, however, allows for 2 other formats

YYYY-MM, the one I used

and

YYYYMMDD

I'll try tomorrow in excel 2010.

I also get the #VALUE! error in both Excel 2002 and 2007.

Maybe it has something to do with regional settings.

 

[Ron Coderre]

Yes, it works. I'm using excel 2000.

Your formula returns the date in the usual international format:

YYYY-MM-DD

The ISO 8601, however, allows for 2 other formats
YYYY-MM, the one I used
and
YYYYMMDD
I'll try tomorrow in excel 2010.

Evidently, it stopped working, beginning with Excel 2003.
Even if I enter this formula: =--"2011-04"
I get a #VALUE! error.

This works, though: =--"2011-04-1"