MS Excel is an exceptionally intelligent spreadsheet app and it’s not just because it supports a lot of logical arguments. The app can recognize data for what it is, e.g. it knows when it’s dealing with currency, and when it’s dealing with time values. It’s precisely because of this that many of the functions and formulas in MS Excel are so useful. They don’t just add and subtract numbers but can also be applied to time and currency. This feature is by all accounts useful until it starts incorrectly identifying data. Sometimes, MS Excel will recognize data that isn’t a date as just that. It changes the value in a cell and impacts the formulas you apply. The only way to deal with it is to stop numbers converting to date.
This happens mostly when you enter alphanumeric values in a particular format but isn’t limited to just that. For example, if you write 02-02 in a cell, t will automatically be recognized as February 2nd. The cell will then recognize all data you enter in it as a date or time value.
The formulas you apply to the cell are subsequently affected because they aren’t dealing with numbers. Instead, they’re dealing with an entirely different type of data. The solution to this problem is oddly simple.
First, select the cell with the incorrectly identified data. If you look at the dropdown in the ‘Number’ toolbox, it reads ‘Custom’. It doesn’t even read ‘Date’ or ‘Time’, and this is what the problem stems from.
Open this dropdown and select the ‘Text’ option. The data you entered in the cell will change into a number. It’s the equivalent of the date that Excel recognized. Delete the figure you see and enter your original data again. This time, with the cell set to recognize everything as ‘Text’, it will not convert to a date or time, or anything else.
This doesn’t solve every problem you might face with the data in the cell. When you apply a formula to the cell, the data is still recognized as a date except, Excel converts it to the numerical value for that date. The formula you apply isn’t going to give you an accurate value. There’s no easy way around this and you will likely have to reformat the way you select values in your formula, or change how you enter them in a cell.
There’s a lot of trial and error involved here if you have to apply a formula however, if you’re only concerned with presenting data, setting the cell to recognize data as text will do the trick. This setting will persist no matter which device you view the sheet on.
