CODEWITHSUNDEEP
excelexcel-2010
Santosh
Santosh

Reputation: 12353

Stop excel converting Accounting or Number format cells to Date

enter image description here

I have a sheet which is locked except for cells B2:B4. Users are expected to enter numeric values in them. When a user incorrectly enters a date in these cells, the format changes to date. Now, even if user removes the date and types in the correct numeric values it appears to be date format. How this can be handled in a better way so that format remains as numeric or accounting and the total below works.

P.S. Can't use vba in the file and the dollars can range between (- 1000000000 to 1000000000)

Upvotes: 1

Views: 968

Answers (1)

HarveyFrench
HarveyFrench

Reputation: 4578

I observed similar behaviour (Excel 2013)

When my cells had format of number or currency and I entered 1/1/2015 the cells both showed 42370.00 not a date. When the cell formatting was accounting this was not the case (and the data was displayed as a date).

I tried using data validation to prevent dates being entered, and couldn't stop them.

So as you observed, accounting behaves poorly. Would your users settle for using the "Currency" format? as this behaves better. (The only different I can tell is that the currency symbols do not line up and cell alignment effects them).

Sorry not to be able to give you the answer you were looking for!

You might consider using data validation to WARN the user if they are entering data that coul dbe a date (ie they enter a number in the range representing the dates that they tend to enter!) Something like "The number you entered can represent a date did you accidentally enter a date not the number"

Upvotes: 1

Related Questions