time data validation

Question

hi,&nbsp;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 less than 30 minutes intervals.meaning - if he entered starting hour of 9:00, he cannot enter ending hour of 9:25, or 10:15.&nbsp;

hansvogelaar · Answer

ecotrad550&nbsp;
Why would 10:15 not be allowed?

ecotrad550 · Answer

HansVogelaar&nbsp;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.&nbsp;&nbsp;

riny_van_eekelen · Answer

ecotrad550&nbsp;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.&nbsp;=D4=MROUND(D4,30/1440)&nbsp;and copy that format to all other cell where the time entry should follow this rule.&nbsp;

ecotrad550 · Answer

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)?

riny_van_eekelen · Answer

ecotrad550&nbsp;Try this rule then:=OR(ROUND(MOD(E4-D4,30/1440),15)=0.020833333333333,ROUND(MOD(E4-D4,30/1440),14)=0)

Forum Discussion

time data validation

5 Replies

Resources