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

 

[AlanY]

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

but can you not add the 2 together as 1 for the calculations?

 

[footoo]

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

Also, depending upon what calculations you are doing, an extra column might not be necessary.
For example, if a total of the text column is required : =SUMPRODUCT((A1:A3)+0)

 

[phras81]

Thank you for all the ideas so far. Having 2 separate input boxes for the time will not work in this case I am afraid. There are other sheets outwith my control that reference the times in these cells. To change the layout would mean massive collaboration and changes. Is there not a way to just restrict the input to time value only and not accept 0.6 for example as an input?

 

[steve the fish]

Only thing i can think of is to use a spare cell with validation such as footoo described earlier using text then putting a formula in your original cell:

=0+newcell

That would work i suppose. A lot of excel formulas could actually coerce a textual time into a real time.

 

[kvsrinivasamurthy]

Try data validation with formatting the cell for time.

For cell P1, formated for time.

DATA --> Data validation
Custom
Formula is
=AND(P1>=0,P1<1)

 

[phras81]

Try data validation with formatting the cell for time.

For cell P1, formated for time.

DATA --> Data validation
Custom
Formula is
=AND(P1>=0,P1<1)

I have tried using this custom data validation formula. On my sheet the cell I am trying to lock down to only allow time is H14 so changed the P1 to H14. I also formatted that cell as time. When I input 0.6 into the cell it is still accepted and the time of 14:24 displayed. Can you help and am I doing something wrong?

 

[kvsrinivasamurthy]

What actually wanted to disply when 0.6 is entered.

 

[steve the fish]

0.6 and 14:24 are the same. If you type 0.6 into a cell it is a valid time. There is nothing you can do as far as I'm aware as stated in post 2.

 

[kvsrinivasamurthy]

Format the cell for text.
Data validation.

Data --> Data validation
Custom --> formula
=AND(ISNUMBER(FIND(":",A3)),LEN(A3)<=5,,LEN(A3)>3)

Now entering in time format is must. Number in any format will notbe accepted.
For clculation purpose use

=Value(A3)