MS Excel365 Date/Serial Date - anyone else experiencing?

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?

D.ozer

Posted 2019-03-13T00:10:25.027

Reputation: 1

Works like that here as well. – Michael Frank – 2019-03-13T00:30:31.277

3What 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 – 2019-03-13T00:49:17.430

1I 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. – 2019-03-13T00:54:44.817

perhaps the update broke the add-in/trigger – Ted D. – 2019-03-13T00:56:18.020

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 – 2019-03-14T21:44:03.673

Answers

-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.

Rajesh S

Posted 2019-03-13T00:10:25.027

Reputation: 6 800

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 – 2019-03-13T08:43:25.540

@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 S – 2019-03-14T06:50:00.597

Asked: 2019-03-13T00:10:25.027

Viewed: 82 times

Active: 2019-03-13T08:39:03.737