When is a number is not a number?

[langto00]

I have an Excel spreadsheet report downloaded out of a cloud based CRM/Practice Management app called Karbon (karbonhq.com). The report contains 3 columns with dates. The cells with the dates are formatted as 'Number' but the entry appears in date format as follows: 04 Jan 2018. This cell entry IS an actual value as calculations can be performed using these cell entries. The only thing that apparently cannot be done is to reformat the cell to another number format.

If I try to change the format of the cell by selecting a different number format from the Number drop-down box on the ribbon there is no change in the cell display regardless the number format option selected. If the cell is edited (F2) and then immediately saved, the entry is converted to the correct date as a serial number. How can all of the dates be converted to an actual number. Any idea what I am missing?!

A sample of the spreadsheet can be seen here:

https://www.dropbox.com/s/k4j2nkw0ljkv68n/2018-01-08_0647.png?dl=0


Excel 2016 MSO (16.0.8730.2046) 32 bit

 

[Joe4]

Welcome to the Board!

That behavior means that the entries are actually text entries, and need to be converted. Simply changing the format of the cell does NOT change the data type. The Formatting is only applied to values which are already entered as numbers (and it is important to understand that Dates in Excel are really stored as numbers, specifically the number of days since 1/0/1900 and simply have a date format on them).

This is a common occurrence with data that originated from other sources. Many times, it all comes into Excel as Text.
You can convert the whole column at once by selecting the column, then selecting "Text to Columns" from the Data menu, and clicking finish. Note that you can only do this one column at a time.