Want date in format mmm-yy

V

vbanovice123

Board Regular
Joined
Apr 15, 2011
Messages
91
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
 
Sort by date Sort by votes
T. Valko said:
I also get the #VALUE! error in both Excel 2002 and 2007.

Maybe it has something to do with regional settings.
Click to expand...

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.
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Ron Coderre said:
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"
Click to expand...

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

In excel 2000 I have no problem with: =--"2011-04"
 
Last edited:
Upvote 0
T. Valko said:
I also get the #VALUE! error in both Excel 2002 and 2007.

Maybe it has something to do with regional settings.
Click to expand...

... 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.
 
Upvote 0
pgc01 said:
... 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.
Click to expand...
OK, thanks for letting us know. :cool:
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Date format issue in a worksheet
Replies
5
Views
291
sharshra
S
A
  • Question Question
Rename sheets by cell value in date format
Replies
1
Views
184
Kaper
Kaper
S
  • Solved
Conditional formatting - Automatic banding of months
Replies
4
Views
168
etaf
etaf
D
  • Question Question
I need to reference sheets with names that are dates, but cannot get formula to work!
Replies
3
Views
171
RoryA
RoryA
A
  • Question Question
How restrict copy data from form to sheet based on five days within current month
Replies
3
Views
110
Ali M
A

Forum statistics

Threads
1,224,525
Messages
6,179,317
Members
452,905
Latest member
deadwings

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top