Custom Format to show only entered digits but never a time/date?

[SteveInAlabama]

I have a Read-only, Worksheet Protected, data entry form, with a cell for Units Produced which can be fractional.

The values for that cell could be, for example: 0, 1, 1.5, 2.75, 200000, etc. The cell has a Data Validation requirement that the value be a number greater than or equal to zero. I left the cell General format because I don't want trailing zeroes if not required. If I used Fixed Format of 0.00, for a large number the cell would display 200000.00 which is harder to read.

However, using General, I have discovered that a user can accidentally enter a value that Excel interprets as a Time. Excel then very unhelpfully formats the cell as a Date format even though Worksheet Protection is on. This is NOT what I want. From that point on, until the worksheet is reopened, the cell remains in Time format.

So, I need a format that will show a number as entered but never as a Time. I know I could manage this with VBA, but thought there might be a custom format that would work.

Any thoughts?

 

[Pimagine]

Maybe you can format the cell as TEXT and use the following data validation requirement (supposing the cell to be validated is A1):
Custom criterium: =AND(VALUE(A1)>=0, ISERROR(SEARCH(":", A1, 1)))

 

[SteveInAlabama]

Unfortunately, some users like to use the cell to do a bit of arithmetic so the TEXT format idea would defeat that capability. However, your logic would work fine for a VBA Worksheet Change event. Perhaps my only option is to use VBA.

 

[Jonmo1]

try custom format of

General;;General

 

[SteveInAlabama]

That worked perfect! Forgot about the multiple format system. Thanks!!!