Hi all,


I am experiencing an issue with date formatting in EXCEL 2003.
I have a cell which I have custom formatted to dd-mmm which 'should' return 04-Jan when 4/1 is entered into that cell...Yes???
My problem is that I am getting 1-Apr as the result...???

Suggestions??????

you shoud enter data according to your regional settings in control panel...
if you are in USA, 1/4/2012 means 4 jan 2012.
or, here in Romania, i use 4/1/2012 for 4 ian 2012.
Then, if you enter 4/1, this means you first enter month!!, that's why it shows 1 -apr. You can format the cell to show date in your custom way (the excel number for that date is the same, no matter how YOU CHOOSE TO SEE that number), but data should be entered according to reg. settings.

Yes I have checked that....
It is set as English (Australia) so by entering my date(s) as 4/1 it 'should' be showing in the cell as 4-Jan as my formatt for that cell is d-mmm........

Can you upload a sample of your worksheet that reproduces this problem?

See attached

Attached Files

Book1.xls (74.0 KB, 16 views)

My guess it's only from regional settings...
Have another check of those settings:
control panel--> regional and language options-->formats tab (pay attention to the date samples , does it show for today , short date 5/1/2012 ? If not, click Customise this format button on this tab, then Date Tab. I cannot see another reason...

Yes agreed.........I cannot understand why it is so
I have attached a screen dump of the said settings.
I even changed them to other versions of English and back to the current (Australia) but alas no good.........Got me stumped!


Anyone else out there have any idea??? Heeeeeellllllppppp....

Attached Files

Doc1.doc (57.5 KB, 13 views)

If you enter 40912 in a cell and format as dd-mmm, what happens?

Your underlying value is 41000 which is April 1st

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

By enetring 40912 in the cell I get 01-Apr

Hi lyngio,

The culprit is your 'Worksheet_Change' macro. Change:
Target(1).Value = UCase(Target(1).Value)
to:
If Not IsDate(Target) Then Target(1).Value = UCase(Target(1).Value)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Sorry it's taken a while for me to get back but I've been on holidays...
Anyways, Fantastic........Works a treat now!!