Using an Input Mask

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

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

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.

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.

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?

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

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

Thanks!

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

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

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.

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.

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

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

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.

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!

Perfect! Just what I needed!!

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!

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