0/1/1900 Problem

Question

Hi

In my Excel 2016 Spreadsheet anytime i type a date in a cell and pressed enter it will not show me the date i input instead it will display 0/1/1900.I have read so many stuffs on the internet about resolving it but all to no avail.I have unchecked both the Transition formula entry level and transition formula evaluation in the advanced setting but the problem still persist.please i need your help in resolving this problem.

Regards

Answer 1

HI

Vijay_Verma thanks a bunch i actually put an equal sign in front of the date as a result excel returns it as a formular. All tnx 👍 to joeu2004 for that tip.

Answer 2

Hi joeu2004 thanks a bunch I was  actually putting an equal sign in front of the date.Its ok👌 now. Cheers ��

Answer 3

Before you try any of the draconian steps that Vijay suggests, let's consider more-likely common user errors.

Suppose you enter today's date incorrectly as a formula, to wit:  =16/6/2019 (with an equal sign) instead of simply 16/6/2019.

When the formula is displayed as a Short Date, you will indeed see 0/1/1900 (in DMY form).

The reason is:  =16/6/2019 is correctly interpreted as the calculation 16 divided by 6 divided by 2019.  Since the result is less than 1, it is interpreted as day zero when it is formatted as a date.

Bottom line:  when you enter dates, do not prefix them with an equal sign.

Answer 4

Hi TundeAkinleye

I am Vijay, an Independent Advisor. I am here to work with you on this problem.

Please try these options one by one and check after each option.

1. Sometimes, add-ins can cause problems. To determine, if add-in is a problem start your application in safe mode and see if problem has gone away.

To start in Safe Mode -

Hold CTRL key and click on application icon and don't release the CTRL key till it asks you for Safe mode confirmation.

If yes, then start application normally and disable add-ins one by one and start application again every time you disable an add-in to determine the culprit add-in.

To disable add-in > File > Options > Add-ins > Down below you have Manage: Com Add-in : Press Go > Untick the Add-ins to disable and tick to enable them

2. Please look into C:\Users\User_Name\AppData\Roaming\Microsoft\Excel (replace user name with your user name, you can directly reach here by copy and pasting following without quotes in Explorer address bar - "%appdata%\Microsoft\Excel") and see if is contains few files / folders. Move those files to some other location (i.e create a backup of those files / folders and delete all files / folders from here). Hence, make Excel folder blank.

Now open Excel and see if the problem disappears or not.

Note - If the above path is not applicable for your installation, you need to find XLSTART path. Excel folder contains XLSTART folder in itself (In case, your XLSTART path is not in Appdata)

Open Excel > ALT+F11 > If Immediate Window is not visible, type CTRL+G. Same can be also be accessed through View > Immediate Window > Type "? application.StartupPath" without quotes and press enter > You will have your path below.

3. Try repairing your office installation (Quick repair is good enough). Try repairing your Office installation. First try Quick Repair (takes 5 - 10 mins) , if that doesn't help then try Online Repair (Takes more than 30 mins but less than 1 hour in general)

https://support.office.com/en-us/article/Repair...

Do let me know if you have any more question or require further help. If above didn't work, I will supply more troubleshooting steps to solve this problem.