#1
|
|||
|
|||
Date formating problem in EXCEL 2003
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?????? |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
Quote:
Quote:
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........ |
#4
|
|||
|
|||
Can you upload a sample of your worksheet that reproduces this problem?
|
#5
|
|||
|
|||
See attached
|
#6
|
|||
|
|||
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... |
#7
|
|||
|
|||
Quote:
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.... |
#8
|
||||
|
||||
If you enter 40912 in a cell and format as dd-mmm, what happens?
Your underlying value is 41000 which is April 1st
__________________
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 |
#9
|
|||
|
|||
By enetring 40912 in the cell I get 01-Apr
|
#10
|
||||
|
||||
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] |
#11
|
|||
|
|||
Quote:
Anyways, Fantastic........Works a treat now!! |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
word 2003 date picker | nashville | Word | 16 | 04-06-2012 04:12 AM |
Problem with opening xlsm in excel 2003 | cryjoh | Excel | 2 | 12-13-2011 11:49 AM |
Excel 2003 / 2007 files saving slow on Win 2003 Server | Atradius | Office | 0 | 07-23-2011 07:41 AM |
Excel 07 formating 17 cells "Need Formula" | Raner | Excel | 2 | 05-30-2010 02:07 PM |
Excel Date Alert? | Kelleigh | Excel | 1 | 12-05-2008 09:35 AM |