Excel date entry being treated as text, not number

Topic

New Reply

G’day All,

Microsoft® Excel® for Microsoft 365 MSO (Version 2201 Build 16.0.14827.20198) 64-bit (released 16 Feb 22)

Normally when a date is entered (e.g. by CTRL+; or typing 23/2/22) excel enters the date in it’s internal number format. Using previous example, 23/02/22 would be 44615.00. You can then format the cell to appear however you like.

This has been working fine up until today when I entered the date it appears to be going in as text and not the number and changing the cell format makes no difference at all.

I’ve searched the web and the Ask Woody forums but haven’t been able to find anything on this.

Is anyone else experiencing, or are you aware of a solution, to this issue?

Thanks

Reply | Quote

Replies

Perhaps this will help?

Occasionally, dates may become formatted and stored in cells as text. For example, you may have entered a date in a cell that was formatted as text, or the data might have been imported or pasted from an external data source as text.
…
[How to] Convert text dates with two-digit years by using Error Checking
…
[How to] Convert text dates by using the DATEVALUE function

Convert dates stored as text to dates
[Microsoft Office Support]

Reply | Quote

Thanks for the suggestion. Unfortunately it’s not that. I even tried in a brand new spreadsheet and it shows “23/02/2022” with cell format ‘General’.

Reply | Quote

I figured it out. It was due to a custom regional date/time setting.

Reply | Quote

In case this is not only amusing but also helpful here:

A year ago or so there was a scandal: scientific data (e.g. the code-names of genes in the human genome) were being corrupted in Excel spreadsheets by being turned into what Excel “thought” were the correct words, thanks to its predictive “autocorrect” function that, apparently, the allegedly smart scientists were clueless about. And the data, such as it was, had been used by them to carry out research and write papers accepted and published in reputable science and engineering journals:

For example:

https://theconversation.com/excel-autocorrect-errors-still-plague-genetic-research-raising-concerns-over-scientific-rigour-166554

Excerpt:

“Our research shows autocorrect errors, particularly in Excel spreadsheets, can also make a mess of gene names in genetic research. We surveyed more than 10,000 papers with Excel gene lists published between 2014 and 2020 and found more than 30% contained at least one gene name mangled by autocorrect.”

“This research follows our 2016 study that found around 20% of papers contained these errors, so the problem may be getting worse. We believe the lesson for researchers is clear: it’s past time to stop using Excel and learn to use more powerful software.”

Or maybe it’s past time for some researchers to learn to use Excel?

https://www.automateexcel.com/how-to/stop-autocorrect/

Below are the steps to turn off autocorrect:

Click on the File tab.
Click on Options.
In the Options dialog box, select Proofing.
Click on the ‘AutoCorrect Options’ button.
In the Autocorrect dialog box, within the Autocorrect tab, uncheck the ‘Replace text as you type’ option.

Ex-Windows user (Win. 98, XP, 7); since mid-2017 using also macOS. Presently on Monterey 12.15 & sometimes running also Linux (Mint).

MacBook Pro circa mid-2015, 15" display, with 16GB 1600 GHz DDR3 RAM, 1 TB SSD, a Haswell architecture Intel CPU with 4 Cores and 8 Threads model i7-4870HQ @ 2.50GHz.
Intel Iris Pro GPU with Built-in Bus, VRAM 1.5 GB, Display 2880 x 1800 Retina, 24-Bit color.
macOS Monterey; browsers: Waterfox "Current", Vivaldi and (now and then) Chrome; security apps. Intego AV

Reply | Quote

You could just have linked to your previous post.
https://www.askwoody.com/forums/topic/excel-autocorrect-is-causing-big-trouble-to-scientists/

cheers, Paul

Reply | Quote

I wanted to add the list of steps to stop autocorrect, who might be, perhaps, of some help in this thread we are now, and obviously I could not include that in an old comment already well past its editing time-window. And I like a good story and the one about Excel changing the data in papers written, reviewed and published by (in their own opinion) oh, so respectable and important people (with whom I have dealt much too much, as anyone who wants to do something important, or at least interesting, for real, has to) just delights me no end.

Sorry about that.

Ex-Windows user (Win. 98, XP, 7); since mid-2017 using also macOS. Presently on Monterey 12.15 & sometimes running also Linux (Mint).

MacBook Pro circa mid-2015, 15" display, with 16GB 1600 GHz DDR3 RAM, 1 TB SSD, a Haswell architecture Intel CPU with 4 Cores and 8 Threads model i7-4870HQ @ 2.50GHz.
Intel Iris Pro GPU with Built-in Bus, VRAM 1.5 GB, Display 2880 x 1800 Retina, 24-Bit color.
macOS Monterey; browsers: Waterfox "Current", Vivaldi and (now and then) Chrome; security apps. Intego AV

Reply | Quote