data validation for time but user enters decimal value < 1 eg 0.6

[phras81]

I have a spreadsheet that I would like people to be able to only enter a time value into. If they enter time as 01:35 this is accepted. If they enter 1.5 it is refused as not in a time format. However if they enter 0.6 it will be accepted and show a time value of 14:24. Surely this decimal value should be rejected? Can anyone help please? Thank you

 

[steve the fish]

Time is a fraction of 1 to excel. The reason 0.6 is accepted is that it is a time as you have seen. It is 6 tenths through the day. Im not sure there is a way to differentiate that i can think of.

 

[phras81]

I understand that time is a fraction under normal circumstances. Surely using data validation set as time it would not allow any fraction to be entered and only time format inputs though.

 

[steve the fish]

Not really time is just a fraction of 1 formatted to look like it does. The underlying value is still a fraction of 1.

 

[phras81]

Ok so maybe this is a bug in excel as the input validation is not restricting to time format. Is there a way to ensure that the user only inputs a time and not a decimal? Thank you for your help with this.

 

[AlanY]

is it possible to have 2 cells for input, 1 for hours and 1 for minutes?
and data validated to 24 and 60 respectively

 

[footoo]

Do you need to do any calculations with the time values?

If not, you could format the cells as text and in data validation : =AND(ISNUMBER(A1+0),FIND(":"),A1))

If you need to use the times in calculations, they could be converted to time values in another column with : =A1+0

 

[phras81]

is it possible to have 2 cells for input, 1 for hours and 1 for minutes?
and data validated to 24 and 60 respectively

Unfortunately we need a single time cell as formulas are then run to total hours etc.

 

[phras81]

Do you need to do any calculations with the time values?

If not, you could format the cells as text and in data validation : =AND(ISNUMBER(A1+0),FIND(":"),A1))

If you need to use the times in calculations, they could be converted to time values in another column with : =A1+0

The time input is used for calculations in other cells. Text input would not be possible sadly.

 

[footoo]

The time input is used for calculations in other cells. Text input would not be possible sadly.

But did you consider this :
If you need to use the times in calculations, they could be converted to time values in another column with : =A1+0