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

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:

- Select the cells you want to use for time input.
- Display the Home tab of the ribbon.
- Click the small icon at the bottom-right of the Number group. Excel displays the Number tab of the Format Cells dialog box.
- In the Category list, choose Custom. (See Figure 1.)
- Replace whatever is in the Type box with
**#":"00**(a pound sign, quote mark, colon, quote mark, and two zeros). - Click on OK.

** Figure 1.** The Number tab of the Format Cells dialog box.

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

**Program Successfully in Excel!** John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out *Excel 2013 Power Programming with VBA* today!

Excel allows you to reference data in other workbooks by establishing links to that data. If you later want to get rid of ...

Discover MoreIs your worksheet, imported from an external source, plagued by non-printing characters that show up like small boxes ...

Discover MoreSometimes 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**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

2016-11-11 23:22:19

John

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

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

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

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

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

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

(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 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

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

2016-05-17 07:58:27

Peter Stern

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

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

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.

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

Copyright © 2017 Sharon Parq Associates, Inc.

## Comments