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

 

[pgc01]

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

Maybe it has something to do with regional settings.

Thank you for checking, you may be right.

It's strange, though, that it works in vba, that I guess usually follows the US settings for dates.

 

[pgc01]

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"

Yes, that may be it, maybe it stopped working after excel 2000.

In excel 2000 I have no problem with: =--"2011-04"

 

[Rick Rothstein]

In excel 2000 I have no problem with: =--"2011-04"

In XL2003, that produces a #VALUE! error on my system as well.

 

[T. Valko]

... and also works in vba, I tried in the Immediate Window:
?CDate("2010-02")

Yes, that works in both Excel 2002 and 2007.

Originally Posted by pgc01
In excel 2000 I have no problem with: =--"2011-04"

In XL2003, that produces a #VALUE! error on my system as well.

Same in Excel 2002

 

[pgc01]

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

Maybe it has something to do with regional settings.

... and you were close, it has to be with the settings.

I found the reason.

First, I've tried with excel 2010 in my computer at work and it worked.
I thought it might be the regional settings and changed the locations, US, UK, France and it always worked.

I then remembered that one of the first things I do since many years ago when I install windows in my computers is to change the date format to the international format "yyyy-mm-dd".

That was it. In the windows control panel, if you define the date with the international format "yyyy-mm-dd", the formula (=0+"2010-02") works independently of the location you specify in the settings (or so is the result of my tests).

Conclusion: I've been using the International format yyyy-mm-dd for years and I never had problems independently of the regional settings of the posters. I'll stick to it and forget this "yyyy-mm" format option.

Sorry. The reson is specific to my installation of windows.

 

[Ron Coderre]

Thanks for investigating...and finding the reason.

 

[T. Valko]

... and you were close, it has to be with the settings.

I found the reason.

First, I've tried with excel 2010 in my computer at work and it worked.
I thought it might be the regional settings and changed the locations, US, UK, France and it always worked.

I then remembered that one of the first things I do since many years ago when I install windows in my computers is to change the date format to the international format "yyyy-mm-dd".

That was it. In the windows control panel, if you define the date with the international format "yyyy-mm-dd", the formula (=0+"2010-02") works independently of the location you specify in the settings (or so is the result of my tests).

Conclusion: I've been using the International format yyyy-mm-dd for years and I never had problems independently of the regional settings of the posters. I'll stick to it and forget this "yyyy-mm" format option.

Sorry. The reson is specific to my installation of windows.

OK, thanks for letting us know.