Forum Discussion
time data validation
hi, I want to create a hour reporting sheet. i want that the user will in 1 cell input the starting hour and in the cell next to It the end hour. However, I want to limit the user from entering...
Why would 10:15 not be allowed?
because if the starting hour was 9:30, putting 10:15 as the end hour will mean 45 min. i want him to be able to input only half an hour or an hour, or an hour and a half, etc. intervals of half an hour.
ecotrad550 Let's say the first starting time is in cell D4, use data validation with the following custom rule to accept only time on the half or whole hour.
=D4=MROUND(D4,30/1440)
and copy that format to all other cell where the time entry should follow this rule.
- thank you so much! it's a great solution!!
do you think it's possible to allow the user to enter whatever starting hour he wants on D4, but at E4 he won't be able to put an end hour that won't give a round hour (half an hour, hour, hour, and a half, etc)?ecotrad550 Try this rule then:
=OR(ROUND(MOD(E4-D4,30/1440),15)=0.020833333333333,ROUND(MOD(E4-D4,30/1440),14)=0)
