0

I am having a problem suddenly with all computers at my place of employment.

If I type a number into a cell (A1), such as "121218" excel is not auto-converting to a date. In addition, when the cell is re-formatted to a "Date" format excel is using the entered number as a date in serial number format and returning the wrong value. "121218" is converted to "11/18/2231".

I can find no way to force excel to accept entries as serial numbers, so I don't think it is a setting, and this happened about halfway through the day today on my own and other PCs on the network. For certain Office 365 updated this morning, however dates formatting were working ok earlier in the day. Tried updating Office again (there was another update ready) and it did not fix the issue.

Pic attached to better illustrate this. I typed "121218" into A1 and then converted the cell format to "Date". As you can see the Sample field returns the date as if the number I typed is a serial date. EDIT: In the pic cell C1 reads: "B2=DATEVALUE..." and should say "B1=DATEVALUE..."

enter image description here

Anyone else experiencing this?

Improve this question
5
  • Works like that here as well.
    – Michael Frank
    Mar 13, 2019 at 0:30
  • 3
    What you are experiencing now is normal behavior. The behavior you were experiencing earlier, where you could type in a 6 digit number and have excel convert it to a date, is usually because someone has installed an event-triggered macro to do that. Perhaps some add-in was uninstalled or changed during the day.
    – Ron Rosenfeld
    Mar 13, 2019 at 0:49
  • 1
    I am at a loss to understand. You type in the number 121,218 (but without the comma) and in the past, excel was under the impression this was not a number, but rather a date? As you said, it was not until after the cell was reformatted to be a date cell. When I type in a date I use / or - and doing so in my 365, Excel sees it as a date even in a non formatted cell.
    – Ted D.
    Mar 13, 2019 at 0:54
  • perhaps the update broke the add-in/trigger
    – Ted D.
    Mar 13, 2019 at 0:56
  • Are you IT for your organization and looking for more expert opinions, or are you staff? If you are not IT for your org, you should probably ask them about this. They will be aware of the specifics of your setup and may be able to re-enable any plugins unique to your setup, or advise you whether or not the behavior is normal.
    – music2myear
    Mar 14, 2019 at 21:44

1 Answer 1

Reset to default
-1

Excel stores Date as serial number so it can be used in calculations also.

By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because, it is 39,448 days after January 1, 1900.

That’s the reason, we cannot convert every second serial number into a date. The date limit so far reaches at 31/12/9999, which is a serial number of 2958465.

Solution 1:

=DATE(RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2))

Solution 2:

=TEXT(A2,"00\/00\/00")+0

will return 43446, then apply any appropriate Date Format to Cell/Cells, you get 12/12/18.

Improve this answer
2
  • what you wrote is true, but it does not answer the question "If I type a number into a cell (A1), such as "121218" excel is not auto-converting to a date ... however dates formatting were working ok earlier in the day"
    – Máté Juhász
    Mar 13, 2019 at 8:43
  • @MátéJuhász, since Date format doesn't works and it's not only with Office365 but this happens in 2013 &2016 also that's the reason I've suggested possible solutions to work with !
    – Rajesh Sinha
    Mar 14, 2019 at 6:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .