How to enter just time in a cell

Perhaps I don't understand the problem, but if you define the following custom format:

##":"##":"##

it seems to do what you are asking for.

P.S. If I understand what you want to do, then you have to understand that you are NOT entering time values . . . you are entering simple integers that you want Excel to format to appear like time values. In fact the result you are asking for is still NOT a time format since there is no way to inidicate whether it is AM or PM. That's OK since you don't want to display the input with AM/PM info.

Quote:

---

Originally Posted by Cyberdude

Perhaps I don't understand the problem, but if you define the following custom format:

##":"##":"##

---

Not a very nice format, 100 appears as :1:00, which is odd for anything especially time. A format of 00\:00\:090 as I proposed back in thread #9 showas 00:01:00, which is much neater IMO.

Dave,

You're almost there. Actually, you are there, you just may not realize it.

Clarification: Chip's VBA code will not appear in the Macro list as something you can invoke, rather it executes automatically whenever Excel detects a change in a cell in the selected range on that worksheet. Thus, you enter your 'pure numeric' time value (e.g. 123456) and Excel formats it as you move to the next cell (12:34:56 PM). It can't/won't/doesn't do anything to entries already in the worksheet before you added the code (unless, of course, you edit an entry slightly so the worksheet detects a change).

Try this, clear A1:A9, then, enter a 5 and 6 digit numeric string in A1 and press return to move to A2, and repeat the process several times. Bear in mind that the code has some rules for how it will translate strings with fewer than 6 digits. Some of the numbers on your list fail to translate: 68910 & 345675.

HTH,

Ah ha ... FANTASTIC!!!! I did not know that. I deleted the data then re-entered it and it worked perfectly.

One last request.... How can I supress the AM/PM. I don't need it for this project but it will most likely come in handy for others. Mainly not everyone knows how to enter or read military time.

You guys did fantastic. Thanks....

Dave

Hey there Cyberdude,

Actually your way would work if I needed to use your format in just a few cells instead of the whole workbook. Thanks for you help and input. I am sure I will be able to use it for future reference.

Dave

Quote:

---

Originally Posted by dleckie

One last request.... How can I supress the AM/PM. I don't need it for this project but it will most likely come in handy for others. Mainly not everyone knows how to enter or read military time.

---

Dave,

A good question. I created a macro, changed the format of a time from 12:12:12 PM to 12:12:12, then, stopped recorder and reviewed the code. As you can see, it's a puzzle:[vba]Sub Macro3()
'
' Macro3 Macro
' Macro recorded 9/26/2008 by Ron McKenzie
'

'
End Sub[/VBA]
Bob, any suggestions?

Cheers!

Dave,

Fixing the time formating is pretty easy. Add the second line (see below) and you should get you 'military time' entries.
[vba] .Value = TimeValue(TimeStr)
.NumberFormat = "h:mm:ss"[/vba] This should do the trick for you.

If this resolves your query, please go into the Thread Tools at the top of your page and mark this critter "Solved". Thanks!

Cheers! (and have a wonderful weekend)

Absolutly Fantastic. Works Perfectly. Thanks all for your input and help.

Dave,

Here is a variation on the solution that a user can use to individually format time entries in cells.

Cheers!