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?

9.00 – 9.30 (accepted)
10.15 – 10.50 (rejected)

Excel Tips - Validate 30mins interval in a single cell 0

Obviously, the answer is YES.

Assume the input cell starts from A1,

Select A1

  • Go to Data –> Data Validation
  • On Settings, Allow Custom
  • Excel Tips - Validate 30mins interval in a single cell 1
  • Input the following formula:
'Thanks Haz for a more robust solution in the comment!
  • OK
  • Copy A1 down according to your requirement


The input must be in the format of –, 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 TimeStart 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,).

Excel Tips - Validate 30mins interval in a single cell 2

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:

  • 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.


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,
      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!


  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.


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s