Got the following question from a reader:
Under data validation, is it possible for me to restrict the time duration (in a cell) to be 30mins or less?
9.00 – 9.30 (accepted)
10.15 – 10.50 (rejected)
Obviously, the answer is YES.
Assume the input cell starts from A1,
TIME(,31,)TIME(,30,59) 'Thanks Haz for a more robust solution in the comment!
- Copy A1 down according to your requirement
The input must be in the format of h.mm – h.mm, note the space before and after the hyphen
The formula looks complicated but it is essentially
=MOD((End Time - Start Time),1)<TIME(,30,59)
MOD((End Time – Start Time),1) is used to calculate the time difference that allows End Time falling onto next day. To learn more about this trick, please refer to the blogpost by Gašper Kamenšek.
The result is then compared to
TIME(,31,) TIME(,30,59), i.e. the numeric value of 31 minutes 30 minutes 59 seconds. When it is less than 31 minutes 30 minutes 59 seconds, it is “good enough” should cater all the minutes around the clock to say the period input is 30 minutes or less (provided that second is not an consideration). Thanks again to Haz for the suggestion! 🙂
The expression <=TIME(,30,) should be more intuitive.
However I do not have a solution to avoid the rounding issue for such cases as 08.00 – 8.30 which “incorrectly” reject your input when you set the comparison to <=TIME(,30,).
Please leave your comment if you have a solution for this.
Side topic for discussion:
Many users used to input a range of whatever values (be it time, date, or just simple number) into a single cell, using a hyphen in-between. For example:
Maybe it is easier to input (which I really doubt). This is absolutely NOT what is expected to use Excel as a analytical tool. Why? Because the cost of it is much higher than the benefit you enjoy.
Take a look at the solution above. The monster formula is a result of this bad practice.
If the Start time and End time (in correct time format) are input into two different cells, say A1 and B1 respectively. The solution could be as simple as
=MOD((B1 - A1),1)<
Although it is very likely that you may write a “super (long)” formula to fix your problem, why create a problem you can avoid easily at the first place?
Whenever possible, stick to a simple rule: One cell, One thing.
Thanks, this is exactly what I was looking for.
One thing that I noticed is that in some cases it accepts 31 minutes (like 08.20 and 08.51), and then found out it’s because of rounding errors.
So, instead of TIME(,31,) use TIME(,30,59)
LikeLiked by 1 person
Glad to know you find it helpful!
Thanks so much for telling us the cases of accepting 31 minutes… the rounding issue regarding TIME got me stuck quite often. 😛 Nevertheless, your suggestion in modifying the formula works like a charm! And I have revised my post content accordingly. Thanks again for your input!
This is nice. I never even thought about this as a possibility. Time to look and see where I can enhance some spreadsheets with this.
Thanks for your kind words! So glad that you like it.
Merry Christmas in advance 😀