Allow time input at 15-min intervals only – Data Validation

How to limit user to input time at 15-min intervals, i.e. 00:15, 00:30, 00:45, 01:00, etc.?

We talked about how to limit user to input Time in the previous post.  Let’s go one step further with Custom Data Validation, where you can put a logical formula to determine what can be input.  Only when the formula returns a TRUE result, the input is validated.


  1. Select the range of cells for Data Validation, say A2:A10 in our example. 
  2. Data –> Validate –> Data Validation… 
  3. Allow “Custom”
  4. Input the formula: =MOD(MINUTE(A2),15)=0 

Note: Make sure A2 is the active cell; and it is a Relative Reference (i.e. no $ sign for A2)

Excel Tips - Data valdiation 15 min input (1)

Optional 1) : We may give an instruction to user by input a message in the “Input Message” tab

Excel Tips - Data valdiation 15 min input (2)

Optional 2) : We may also alert user when incorrect data is input in the “Error Alert” tab

Excel Tips - Data valdiation 15 min input (3)

Remember to press OK when all is set.

A message box pop up (like a comment) when the cell with Data Validation is selected.

Excel Tips - Data valdiation 15 min input (4)

A dialogue box pop up when invalid data is input.

Excel Tips - Data valdiation 15 min input (5)

Most likely, users will follow your worksheet design to input the data in a way you need; and thus save you lot of time in cleansing the data before processing. 


So, how the formula works?

MINUTE returns only the minute of a time value, from 0 to 59.

so MINUTE(“1:00”) returns 0, MINUTE(“1:01”) returns 1, etc…

MOD returns the remainder after number is divided by divisor.

By wrapping MINUTE(A2) in MOD with a divisor of 15, i.e. =MOD(MINUTE(A2),15) returns a result from 0 to 14.

Altogether, =MOD(MINUTE(A2),15)=0 returns TRUE only when the minute of the time is either 00, 15, 30, or 45.

The following screenshot describes everything.

Excel Tips - Data valdiation 15 min input (6)


About MF

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 and tagged , , . Bookmark the permalink.

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.