Date formating problem in EXCEL 2003

[lyngio]

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??????

[Catalin.B]

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.

[lyngio]

Originally Posted by lyngio

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??????

Originally Posted by Catalin.B

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

[Catalin.B]

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

[lyngio]

Originally Posted by Catalin.B

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

See attached

[Catalin.B]

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

[lyngio]

Originally Posted by Catalin.B

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

[Pecoflyer]

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

Your underlying value is 41000 which is April 1st

[lyngio]

Originally Posted by Pecoflyer

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

Your underlying value is 41000 which is April 1st

By enetring 40912 in the cell I get 01-Apr

[macropod]

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)

[lyngio]

Originally Posted by macropod

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)

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