Data Validation - 15 Minute Intervals - Time Range

khammonds294

New Member
Joined
Apr 4, 2019
Messages
5
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!
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
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
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
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!
 
Last edited:

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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! :)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,349
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
:) 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!
 

khammonds294

New Member
Joined
Apr 4, 2019
Messages
5
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,944
Messages
5,621,750
Members
415,854
Latest member
Tutu123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top