Has anyone come across any problems with Excel 2002 changing the format of dates for no apparent reason. This happens intermittently even though the date is entered correctly in the first instance (in thsi case a user form) however it will switch todays date (16/11/04) to a US date (11/16/04) for no reason. I cant believe this is some random glitch with Excel and although i have had this problem in the past it is very intermittent.
Does anyone know of a workaround or solution for this at all ? ? ?
I don't know if this is the case with Excel 2002, but I was working for a U.S. based subsidiary of a U.K company in advance of Y2k and I remember Excel getting its default sequencing of dates (regardless of the formatting option which determines how they will display), from the Windows setup. Go to your Control Panel and select Regional Options and select the Date tab and make sure your Short Date follows your desired dd/mm/yyyy. If information is getting entered to this spreadsheet from a user form and thus from a different computer, I think you could have a problem if their regional options are not in sync with yours. Just a thought.
Ive occasionally had this problem when working with dates and using VBA
The way i got round it was to force excel to work with numbers then later format this to dates,
to compund the problem it would only happen to some dates and not them all.
If you are running any code at all then i wiuld suggest doing something like
range.Numberformat = "0"
'do your calcuulation or input or something then
range.numberformat = "dd/mm/yyyy"