Data validation help - stop excel from converting time to decimal?

tfgorman

New Member
Joined
Mar 21, 2013
Messages
1
I need help with a data validation issue.

I am trying to use data validation in some cells to restrict what people can enter into cells. In my spreadsheet, there are cells where times are entered and other cells very near this where number values are entered. I was trying to use data validation to restrict people from accidentally entering a time into a cell that is designed for only numbers.

I have my data validation set up to allow decimal values of numbers between 0 and 500 and the formatting of the cell setup as a decimal format with 2 decimal places. The problem is that when I test my validation and enter a time of 07:00 into the cell, excel then turns this into a decimal value automatically. Because of this, the data validation does not reject the time input in that cell as I want it to. I want excel to keep this time as it is entered, and not automatically convert it to a decimal so that my data validation will reject it, but I can't figure a way to make this work.

Is there any way I can keep excel from turning the time automatically into a decimal value?

I know that I can set the formatting of my cell to text, and that keeps excel from turning the time into a decimal value automatically and keeps them as entered. But if I do that then it treats the numbers I enter into this cell as text as well and then the data validation I have setup rejects all numbers entered into the cell as well.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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
Back
Top