Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Using an Input Mask.

Using an Input Mask

by Allen Wyatt
(last updated May 17, 2016)

18

When inputting time into a cell, it is easy to enter digits—that's what the numeric keypad is for, after all. What can really slow you down is the necessity to enter other characters, particularly ones that require the use of the Shift key. For instance, if you are entering times, it is easy to enter 230 for 2:30, but it is a pain to slow down by entering the colon.

Thus, you may wonder if there is a way to set up an input mask that will add the colon automatically. The good news is yes, there is. The bad news is no, there isn't. Sound confusing? Let me explain...

You can set up a custom format that will display your time in any format you want. For instance, you could use the following steps:

  1. Select the cells you want to use for time input.
  2. Display the Home tab of the ribbon.
  3. Click the small icon at the bottom-right of the Number group. Excel displays the Number tab of the Format Cells dialog box.
  4. In the Category list, choose Custom. (See Figure 1.)
  5. Figure 1. The Number tab of the Format Cells dialog box.

  6. Replace whatever is in the Type box with #":"00 (a pound sign, quote mark, colon, quote mark, and two zeros).
  7. Click on OK.

You can now enter your times using just digits. The problem (and this is the bad news) is that the cell doesn't really contain a time. If you enter 230 (for 2:30), it doesn't contain 2:30 as a time—it contains two hundred and thirty. Thus, you can't use the contents of the cell directly in time calculations.

To overcome this, you can use another column to show the entered digits converted into a time. All you need to do is use a formula to do the conversions. For instance, if the time you entered was in cell A3, you could use the following formula in a different cell to do the conversion:

=(INT(A3/100)/24)+((A3 - (INT(A3/100)*100))/1440)

Format the cell that contains the above formula so it displays one of the various time formats, and you are all set.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12550) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Using an Input Mask.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Comparing Lists for Duplicates

Do you have two worksheets on which you need to see if there is duplicate information? Here is a couple of quick ways to ...

Discover More

Ignoring Smart Quotes when Comparing Text

When comparing two pieces of text, you may find that Word's smart quotes can mess up the comparison. Here's a quick way to ...

Discover More

Hiding Spelling Errors

When you are typing in a document, Word normally checks your spelling in the background, marking possible spelling errors as ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Automatically Breaking Text

Want to convert the text in a cell so that it wraps after every word? You could edit the cell and press Alt+Enter after each ...

Discover More

Easily Dividing Values by 1000

Sometimes the data in a worksheet isn't in the exact format desired. If you want to dividie your values by 1,000, there are a ...

Discover More

Getting Rid of Everything Except Numbers

Got some numbers and letters mixed up in the same cell? You may need to get rid of those letters so you are left with just ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is seven minus 2?

2016-11-11 23:22:19

John

(Continued from comment immediately below)

Oops, the formulas below didn't do well with times between midnight and 1:00 AM.

For starters, #:00 needs to be replaced with 0:00. If all entries are based on a 24-hour clock, then =VALUE(TEXT(A2,"0:00")) should work fine. And this seems to me the easiest way to go with the fewest keystrokes.

If AM and PM values are to be entered, then some means of distinguishing between the two is necessary. As suggested before, PM values could be indicated by an appended minus sign (-), easy to enter since it's available on a numeric keypad. Then the following formula will work for both 12-hour and 24-hour entries:

=IF(ISTEXT(A2),VALUE(TEXT(LEFT(A2,LEN(A2)-1)+1200*(LEFT(A2,2)<>"12"),"0:00")),VALUE(TEXT(A2,"0:00")))

or, equivalently,

=IF(ISTEXT(A2),VALUE(TEXT(SUBSTITUTE(A2,"-","")+1200*(LEFT(A2,2)<>"12"),"0:00")),VALUE(TEXT(A2,"0:00")))

If most of the entries are PM values, consider modifying this approach by letting the minus sign designate AM values in order to minimize keystrokes.

A different way to distinguish between AM and PM entries would be to use a different column for each. This will minimize keystrokes if there's not much jumping between AM and PM. Suppose 230 in column A represents 2:30 AM and in column B represents 2:30 PM. Calculate the correct time value in column C with a formula such as:

=VALUE(TEXT(A2,"0:00"))-0.5*(A2>=1200)+VALUE(TEXT(B2,"0:00"))+0.5*(B2>0)*(B2<1200)

Finally, for any of these approaches, consider data validation of input. Good luck.


2016-11-11 10:19:35

John

Ricaz -- nice thinking, thank you.

Hayley -- you asked about distinguishing AM from PM. Here's an idea for others like me unaccustomed to using a 24-hour clock. (I don't want to have to take time to mentally add 12 for each PM entry.) Append a minus sign to indicate PM.

For example, 230 would represent 2:30 AM whereas 230- would represent 2:30 PM. The trailing minus sign tells Excel that the cell contains text, which can serve as a flag to add 12 hours.

If your entry is in cell A2, you could use either of these formulas for the conversion to a time value:

=IF(ISTEXT(A2),VALUE(TEXT(LEFT(A2,LEN(A2)-1)+1200,"#:00")),VALUE(TEXT(A2,"#:00")))

=IFERROR(VALUE(TEXT(A2,"#:00")),VALUE(TEXT(LEFT(A2,LEN(A2)-1)+1200,"#:00")))


2016-11-10 13:45:53

Ricaz

Hi there!
Just a small note... when I had to enter lot of time data, I avoided using the colon button by using the autocorrect. In fact, I told autocorrect to "correct" the string "/*" into ":", so I can enter the colon with the numpad.
(Unless for some odd reason you ever need to write something that includes "/*" that works fine)

By the way, I also wrote an AutoHotkey macro to remap the NumLock button of the numpad to work as "backspace.


2016-05-18 04:32:34

Relu

or you can try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static rngLast As Range
Dim rngVal As Double
If Not Application.Intersect(Target, Range("A1:A100")) Is Nothing Then
If rngLast.Count <> Target.Count Then GoTo exit_Worksheet_SelectionChange ' Check if is multicells selection
If Not rngLast Is Nothing Then
If rngLast.Column <> 1 Then GoTo exit_Worksheet_SelectionChange ' Apply for column "A", change if want another column
rngVal = rngLast.Value
rngLast.Select
Selection.NumberFormat = "HH:MM"
rngLast = (Int(rngVal / 100) / 24) + ((rngVal - Int(rngVal / 100) * 100) / 1440)
End If
End If
exit_Worksheet_SelectionChange:
Set rngLast = Target
Target.Select
End Sub

and have the result in the same cell.


2016-05-18 00:24:06

John

Peg,

Maybe this will help. Say we're starting with the number 230 in cell A3, which we've entered instead of 2:30 because the colon is just too much trouble to type.

Excel uses 1 to represent one 24-day (starting at midnight). So if you enter 0.5 in a cell and format it as time, Excel will display 12:00 PM (half a day). Conversely, if you enter 6:00 AM in a cell and format it as an ordinary number, Excel will display 0.25, which is one fourth of a day. So we need to come up with the number that corresponds to 2:30, and it certainly won't be 230, which would represent 230 days.

For simplicity, let's start with 2:00. That would be just a fraction of a day, 2/24 ths to be precise. Type =2/24 into a cell and format it as time to see 2:00 AM.

The first part of Allen's formula, =(INT(A3/100)/24)..., divides the value in A3 (230) into two parts separated by a decimal (2.30), then takes just the integer or hours portion (2) and divides that by 24 to get us in the ball park (2:00).

Now we need to add the 30 minutes, which also constitute a fraction of a day. As Juan pointed out below, there are 1440 minutes in a day, so the fraction we need to add is 30/1440.

This is where the rest of Allen's formula, +((A3 - (INT(A3/100)*100))/1440), comes into play, basically getting rid of the 2 in 230. Working from the inside out, A3/100 gives us 2.30, INT(A3/100) gives us 2, and multiplying by 100 gives 200, which subtracted from A3 (230) gives us 30, which can then be divided by 1440.

For this particular case, the formula boils down to =2/24 + 30/1440, or, more generally =hrs/24 + min/1440. We use /100, INT and *100 as tools to help separate the hours and minutes.

Got it? Here's a variation in case you want to test your understanding: =(INT(A3/100)*60 + (A3-INT(A3/100)*100))/1440. Can you come up with yet another variation that does not use the value 1440?


2016-05-17 15:11:17

john

Sarah,

Your parentheses need fixing. You want =(INT(F10/100)/24)+((F10 - (INT(F10/100)*100))/1440)

From the formula you listed below, you'll need to add ")" after "24" and delete ")" after the third "F10".

Or switch to =VALUE(TEXT(F10,"#:00"))


2016-05-17 14:01:22

Hayley

I feel like a dummy now - but changing from a civilian to military clock worked perfectly

Thanks!


2016-05-17 12:40:14

John

=TEXT(A5,"#:00") will convert the number 230 in cell A5 to the text "2:30"

(The backslash specifies that the colon is to be interpreted as text. Two double quotation marks on each side have the same effect. For example, =TEXT(A5,"#"":""00 ""PM""") will produce the text "2:30 PM")

Better yet:

=VALUE(TEXT(A5,"#:00")) will convert 230 to 0.104166667, the correct value for 2:30 AM which can be formatted as desired.

One way to distinguish between AM and PM would be to enter numbers based on a 24-hour clock, where 1430 would represent 2:30 PM.

As an aside, when entering a column of dollars and cents, skip the commas and decimal points but always include the cents, for example 450 for $4.50 and 123400 for $1,234.00. Afterward, paste special divide by 100 to convert from cents to dollars, thus inserting all the missing decimal points in one fell swoop. Format the column to display dollar signs or commas as desired.


2016-05-17 12:31:32

Brenda

Hey, DPark
What are the chances you can share some of your codes?


2016-05-17 12:15:00

DPark

I work in state government and the organization uses numerous codes for different types of expenditures. Many of the codes have leading zeros. Most of the Excel users enter the code with leading zeros as text, such as "004" and continue in this manner until code 100 or above is reached. The problem with this is that the codes cannot be sorted correctly. I have introduced masks for the various codes, which reduces input time (enter 4 and get 004 displayed) and allows for correctly sorting the codes in numerical order.

I have used masks for many other unique coding requirements for my organization. Once created and put into common use, this is a terrific Excel feature to know and use.


2016-05-17 11:26:43

Sarah

Help please. When I do the second part -- converting to a time format by inserting two new columns with =(INT(F10/100)/24+((F10-(INT(F10)/100)*100))/1440), the minutes only display "00." For example, 5:30 - 6:45 converts to 5:00 - 6:00.

Thank you.



2016-05-17 11:17:11

Hayley

This may be a dumb question, but how do you get it to figure AM vs PM?


2016-05-17 10:26:56

Jeaux

LOVE this. I have tried so many work-arounds over the years and this is the best.


2016-05-17 08:23:57

Peg Molter

I love the tip above, but in trying to perform it step-by-step, I must be getting the order of operation - out of order, since I can't make the answer come out correctly. Could you show the steps in order of precedence? Thanks.


2016-05-17 07:58:27

Peter Stern

Allen, great tip. Graham, isn't this more universally called an "Octothorpe"? Although I must admit, I like that tic-tac-toe thingie a lot better.

On a related topic, when I receive word documents from the UK, it drives my spell-check crazy; I would change the language, but I am way too amused!


2015-03-11 13:02:44

smj

Perfect! Just what I needed!!


2013-03-16 11:33:46

Graham Hall

Allen - just beware that the term "Pound sign" means something very different in the UK - it's the symbol for the currency (GBP).

In the UK tech guys and twitter users would call "#" the "hash sign," although very few other people would recognise the term.

Perhaps best described as the "noughts and crosses" sign for UK readers?? (referring to the game North Americans call tic tac toe)

Two peoples separated by a common language I'm afraid!


2013-03-16 08:53:37

Juan

Very useful tip. It's divided over 1440 to convert 30 minutes to day (1 day has 24 hours = 1440 minutes (60*24))


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.