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.
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.
