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

SteveInAlabama

Board Regular
Joined
Aug 22, 2002
Messages
94
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?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Pimagine

New Member
Joined
Dec 11, 2013
Messages
25
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

Board Regular
Joined
Aug 22, 2002
Messages
94
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.
 

Forum statistics

Threads
1,136,354
Messages
5,675,299
Members
419,560
Latest member
g3org

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top