## How to limit time interval input in a single cell?

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?

Example:
9.00 – 9.30 (accepted)
10.15 – 10.50 (rejected) Assume the input cell starts from A1,

Select A1

```=MOD(TIME(MID(A1,FIND(".",A1,5)-2,2),MID(A1,FIND(".",A1,5)+1,2),)-TIME(LEFT(A1,2),MID(A1,FIND(".",A1)+1,2),),1)<TIME(,31,)TIME(,30,59)
'Thanks Haz for a more robust solution in the comment!```
• OK
• Copy A1 down according to your requirement

Limitation:

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

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,). ### 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:

• 8:00-9:00
• 20/12/2015-25/12/2015
• 100-200

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)<TIME(,31,)TIME(,30,59)`

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. An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips, Formula and tagged , . Bookmark the permalink.

### 4 Responses to How to limit time interval input in a single cell?

1. Haz says:

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)

Liked by 1 person

• MF says:

Hi Haz,
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!
Cheers,

Like

2. Oz du Soleil says:

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.

Like

• MF says: