Microsoft finally fixes Excel's data conversion problem

Microsoft Excel can automatically detect dates, times, currencies, etc. from text and convert them into a parsed format for use in formulas. However, Excel often gets conversions wrong, especially when it comes to dates, which Microsoft is working to fix.

Microsoft revealed this week that it was working on a new option to disable automatic data conversions from Excel, which is now available in Office Insider test builds on Windows. The new setting is available in File > Options > Advanced > Automatic Data Conversion. You can disable each type of conversion individually, and there is also a setting to display a warning before converting data into an imported .CSV document.

The parameter is a much needed addition to Excel, as automatic conversion can be cumbersome. For example, entering ID numbers or other identifiers with leading zeros may cause Excel to remove leading zeros. Excel also converts dates such as "July 7, 2022" to date strings such as "7-Jul-22".

Microsoft said in a blog post, "We've consistently heard from customers over the years (and probably some of you!) that they're frustrated that Excel automatically converts data into specific. While we don't change Excel's defaults, we give you the ability to disable specific types of automatic data conversions as needed. This way, you won't have to worry about converting your data to a format you didn't want and didn't expect.

Excel's automatic data conversions, especially related to dates, have been the butt of jokes for years. The HUGO Gene Nomenclature Committee, the group that names genes in human DNA, renamed several genes in 2020 because Excel kept converting them to dates.

excel: is it a date?

me: 57.39 is really not a date

excel: strong dating vibes for me

me: how

excel: corrected

me: 57/39/2020?

excel: nothing

— slate (@PleaseBeGneiss) November 17, 2020

Even though date conversion is the most contested feature, there's no option yet to disable it – Microsoft says it's "planned but not yet available". The new settings also don't work with macros at this time.

The new data conversion options are available to Office Insiders running Excel version 2207 (Build 15427.20000) or later on Windows. There's no timeline at this time when it will roll out to everyone with Excel, and Microsoft says the feature is still on the way for Mac. The company hasn't mentioned when (or if) it will be available in Excel Web or mobile apps.

Source: Office Insider Blog