Results 1 to 8 of 8

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

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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. #2
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default 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
    √-1 2³ ∑ π
    …And it was delicious!

  3. #3
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default 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!
    Last edited by tyija1995; Aug 20th, 2019 at 08:34 AM.
    √-1 2³ ∑ π
    …And it was delicious!

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,357
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Data Validation - 15 Minute Intervals - Time Range

    Quote Originally Posted by khammonds294 View Post
    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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Data Validation - 15 Minute Intervals - Time Range

    Quote Originally Posted by Peter_SSs View Post
    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!
    √-1 2³ ∑ π
    …And it was delicious!

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,357
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Data Validation - 15 Minute Intervals - Time Range

    Quote Originally Posted by tyija1995 View Post
    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 by Peter_SSs; Aug 20th, 2019 at 08:45 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Data Validation - 15 Minute Intervals - Time Range

    Quote Originally Posted by Peter_SSs View Post
    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!
    √-1 2³ ∑ π
    …And it was delicious!

  8. #8
    New Member
    Join Date
    Apr 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation - 15 Minute Intervals - Time Range

    Quote Originally Posted by tyija1995 View Post
    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!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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