When I type Apr 20 and hit enter the date changes to 1 Apr

My spreadsheets have suddenly started to change the date format.  I am working in an office environment and when opening the spreadsheets on any other computer the dates entered are correct.  I have checked the Excel options and placed a checkmark in the 1904 date settings.  After closing it works for a new worksheet - but does not apply to Excel defaults or any existing spreadsheets (and we have thousands)  EAch time I have to go in and reapply the checkmark for the 1904 date change - and it still doesn't apply to the entire spreadsheet.  I have cleared the cell of all formats - tried reapplying a date format - and still once I type Apr 20 it changes to 1 Apr.  HELP!!!
metreauAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barry houdiniCommented:
Why do you want to use 1904 date format, is there a specific reason?
barry
0
metreauAuthor Commented:
No I was just told that if excel starts jumping years that you need to apply the 1904 date format....I've tried it both ways....
0
stevegrenzCommented:
What about format then cells then under number, choose the date format there that you like and click ok.
0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

barry houdiniCommented:
I don't think 1904 date format would make any difference to what you describe. You say you type 20 Apr.....is it exactly those characters in that order?
If you format the cell as general after you put in the date what do you see?
regards, barry
0
Anthony MellorChartered AccountantCommented:
This problem can be a real pain.

1904 date system is Apple Mac and the problem described is a pain when dealing with PCs and Macs.
The properties are for the file, but every time one gets a file back from a PC user it's "been reverted" to the 1900 pc system, not surprisingly. This means all dates are 1462 days out.

The preference affects only the active workbook.

Also as the asker says, formatting won't do it.

I am hazarding a guess here that these sheets may contain dates entered under both systems (when inputting, the date system may not be so apparent or a matter of focus), so some under 1900 and some under 1904 which happens. The fix for that is a +/- 1462 days formula process. If the dates are within known ranges then an IF can be used to change dates outside the range.

To fix "thousands" of files is going to need VB I would imagine.
An example file would help, also if there are any "rules" we can use to test dates, also is this indeed a PC/Mac mix? Or is it some Macs being run on the PC system and some not?

Still trying to remember why the 1904 checkbox doesn't always (appear to) apply to the whole sheet. It sounds familiar...

Anthony

0
sametcCommented:
Check the Region and Language options in the control panel?  Make sure it didn't get changed and you are using a different format.
0
RichardSchollarCommented:
Hi

What do you have for Short Date foprmat under your Regional Settings accessed via Start>Control Panel>Regional and Language Options>Regional Options tab>Customize button and towards the bottom of the dialog (this is under XP).  I can replicate what you are seeing if you have a short date format of dd/mm/yy but not if mm/dd/yy.

Richard
0
Anthony MellorChartered AccountantCommented:
Here's why setting the 1904 date format sometimes fixes the problem (in part of the sheet or file) and sometimes not.

Under the 1900 system (standard PC) all dates will be entered with a date number 1462 days less than if entered in the 1904 date system.

Under 1900 system

1/1/1900      1.00
2/1/1904      1,463.00

1/1/1900      dates accepted as text and not date numbers until
2/1/1904      1 being 1462 days less than under the 1900 system

The option 1900/1904 defines the date number creation. Once entered, the date number will not be altered, but the date displayed by it will. So for the same date number there are two possible dates for display (except 1/1/900 to 1/1/904 when only the PC system will generate a date number)

Anthony






0
Anthony MellorChartered AccountantCommented:
p.s. after first pair of dates I have missed a sub head of " Under the 1904 System"
0
Anthony MellorChartered AccountantCommented:
pps Apr 20 is being interpreted as 1 APRIL 2020 - this is not related to the 1904 / 1900 date system issue.
0
metreauAuthor Commented:
Thanks for your suggestions...however:

I have changed the date format to general applied and nothing changes.  Checked the regional settings everything is correct - again this is only happening on the one station - all other stations the date applies correctly.  I have tried looking to see if the station is missing a microsoft patch - but nothing seems different.

When I type 20 apr it changes to 20-Apr - what I want - if I type Apr 20 it changes to 1-Apr and in the formula bar it reads 1-April- 2020 - this is incredibly frustrating!  I have told the user to try to type dd mmm but her habit of mmm dd is hard to break!
0
Anthony MellorChartered AccountantCommented:
put this in to a cell and reference it to an errant date cell:

=CELL("format",E29)       where E29 is said date cell and see what format it reports, try this in a file that works and one that doesn't: let's see if the formatting is different in one that works and one that doesn't.

Date entries without the year should (ha ha) default to the current year (what year does this computer think it is?)

Does setting 2 or 4 digit year change anything?

Anthony
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
metreauAuthor Commented:
I will let you know how I make out - home sick...

0
Anthony MellorChartered AccountantCommented:
ok - upload a "problem file" if you like, we can look at it while you take time out. Anthony
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.