Data Validation

[trebor1956]

Hi, I have a s/sheet for entering time in format [h]:mm

Is there a way a data validation that I can ensure users enter the time as hour : mins (using a colon) instead of accidentally using hour . mins ?

Many thanks

 

[MUKESHY12390]

yes, there is way by using Data Validation -Allow---> "Time" --> Between (Data option).

 

[trebor1956]

Hi MUKESHY12390,

I have looked at that option but I can't get it to work. Because an amount of time is what is being entered it could be from 1 minute up to 24 hours. Using the validation you suggest I can't put in 24:0 I get an error msg saying "must enter a valid end time"

 

[MUKESHY12390]

go through the link...


https://wmfexcel.com/2014/08/10/data-validation-time/

 

[trebor1956]

Hi,

Thanks. That does work. I was hoping there was a way to simply enforce using a colon and then supplying any number of hours. However, your solution will work for me, many thanks.

 

[MUKESHY12390]

I'm glad it worked out.