When Excel lies about Dates and Percentages

[jase71ds]

Wondering if any of you have others experienced the following...
3-4 years ago, while using imported dates from a database in Excel 2003 (or maybe 2010 - can't remember) I realized that some of my dates were not calculating. I checked their properties - Excel said they were in fact dates. I spent hours wracking my brain, trying things, and on Google. Finally I came across someone else who had the problem. Buried in all the Replies, was a short comment where the user suggested selecting the afflicted cells, and doing a Find-Replace. Finding the slash mark "/" and replacing it with the very same slash mark "/".
I tried it, and holy-cow, it worked! Now Excel would actually calculate with those afflicted dates.

Fast forward to today. I realized that some calculations with percentages appeared to be off from last month's calculations. After several hours of beating my head, I finally realized that, once again, Excel (2016) was telling me that all my column was %, but in fact about ten percent of them were fraudsters - Text. (Yet when I looked at their cell properties it said they were percent) They would not calculate. So I remembered how I resolved the date dilemma that I had with dates, and I did a Find/Replace "%" with "%".

It worked!

Am I alone in this, or have others experienced such? Does anyone know what causes Excel to do this, and then lie about it in the cell properties? This is not cool

 

[Scott Huish]

I assume you are looking at the cells Number Format. Formatting text as a % doesn't actually change text to a %. That format will only affect actual numbers, not numbers that are a text string.