# Thread: Data Validation - 15 Minute Intervals - Time Range Thanks: 0 Likes: 0

1. ## Data Validation - 15 Minute Intervals - Time Range

I have a column of cells in which I need users to input their time spent on a project ( time input as [hh]:mm ) the hours are accumulated over a 6 month period so can exceed 24 hours - but I would like the times entered to be restricted to 15 minute intervals (eg. 60:00, 55:15, 43:30, 38:45 etc.)

I've set tried using the formula =MOD(MINUTE(G3),15) where column G3 is formatted [hh]:mm and wonder if the brackets around the HH are preventing the MOD formula to work - but I cannot think of a work around.

Can anyone help?

Thanks!

2. ## Re: Data Validation - 15 Minute Intervals - Time Range

Hey,

Perhaps a bit wacky but try:

=MOD(G3,(15/24/60))<0.000000001

Sometimes the result is very close to 0 when you put in a number rounded to 15mins, e.g. 01:30 gives 3.47E-18 result... There might be a cleaner way around this though

3. ## Re: Data Validation - 15 Minute Intervals - Time Range

Testing it on my end it goes a bit weird, try this instead:

=ABS(INT(G3/(15/24/60))-(G3/(15/24/60)))<0.01

Hope it works for you, let me know!

4. ## Re: Data Validation - 15 Minute Intervals - Time Range

Originally Posted by khammonds294
I've set tried using the formula =MOD(MINUTE(G3),15) where column G3 is formatted [hh]:mm
In any case, wouldn't you need
=MOD(MINUTE(G3),15)=0

5. ## Re: Data Validation - 15 Minute Intervals - Time Range

Originally Posted by Peter_SSs
In any case, wouldn't you need
=MOD(MINUTE(G3),15)=0
I feel like a bit of a fool playing around with ABS and INT now! I guess I had a bit of a donut moment going around a different route when unnecessary, thanks Peter, learn something new everyday!

6. ## Re: Data Validation - 15 Minute Intervals - Time Range

Originally Posted by tyija1995
I feel like a bit of a fool playing around with ABS and INT now! I guess I had a bit of a donut moment going around a different route when unnecessary, thanks Peter, learn something new everyday!
Well, your idea of using < a small number may be a good one. I guess it is possible for the MOD function to not return exactly zero when it should, though my testing didn't turn up any. The Data Validation should work just as well and possibly be safer to use something like

=MOD(MINUTE(G3),15)<0.0000001

7. ## Re: Data Validation - 15 Minute Intervals - Time Range

Originally Posted by Peter_SSs
Well, your idea of using < a small number may be a good one. I guess it is possible for the MOD function to not return exactly zero when it should, though my testing didn't turn up any. The Data Validation should work just as well and possibly be safer to use something like

=MOD(MINUTE(G3),15)<0.0000001
Agreed - the first formula I wrote was bugging me because every now and then it would return incorrectly, I think 55:00 returned 0.010417 (coincidentally that is 15/24/60) so bizarre! Clearly it should have been 0.

But then 54:00 would return 1.25E-16 and 01:00 returns 0, I suppose this is what made me divert in to another path!

8. ## Re: Data Validation - 15 Minute Intervals - Time Range

Originally Posted by tyija1995
Agreed - the first formula I wrote was bugging me because every now and then it would return incorrectly, I think 55:00 returned 0.010417 (coincidentally that is 15/24/60) so bizarre! Clearly it should have been 0.

But then 54:00 would return 1.25E-16 and 01:00 returns 0, I suppose this is what made me divert in to another path!

Thank you so much!!!! Super helpful!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•