How to enter just time in a cell

[dleckie]

I am using MS Office 2003. I have tried to enter just time into a cell (example 01:13:59) but I have only been able to enter both date and time when the format is set to Time. I am now trying to set the Format to Text (or another Format that will work) and write a VB Macro to place colons (:) between the 8 digits. Can anyone help please.

I hope I have given enough information. just joined.

Thanks for your help.

Dave

[Bob Phillips]

That is very odd, I can enter time no problem. The problem with entering as text is that you won't be able to do anything with it.

Have you tried formatting the input (Cells>Format>Custom)?

[mikerickson]

Excel doesn't recognize the existence of time without a date.

When entering 10:30 PM in a cell, the "0 date" component is interpreted as 0.Jan 1900.

When entering a date/time combo (by typing "5/12/07 9 AM") into a cell formatted "h:mm", I've had the format change to "m/dd/yyyy h:mm", but I've never seen the format change when entering only a time (eg "9:30 AM") How are these times being entered into the cell?

[RonMcK]

dleckie,

As XLD said, this is odd. Have you used Format > Cells > Numbers > Time? Here's a screen shot of the dialog (as I see it on my Mac here at work). It sounds like you're working too hard. ;-)

Thanks,

[dleckie]

Hi XDL,
I have tried Custom. I created one as hh:mm:ss and when I entered the number 121359 hoping to get 12:13:59 I get 4/7/2233 12:00:00 AM

The date still appears 1st. What did you use or do you recommend to use in Custom?

Thanks
Dave

[RonMcK]

Excel stores the date and time as a single number where the quantity to the left of the decimal is Excel's version of the julian number and the right side, the decimal is the time. This is what is throwing you off. Excel assumes that your number identifies a date not a time.

How about entering the delimiter ( : ) at the appropriate places?

[dleckie]

Hi Mikerickson,

Right now I have to enter the time with the colons (:) (example 12:13:59). Its a real pain in the butt. I would rather just enter the number straight with out the colons (:) (example 121359).

What you say is very true. I ALWAYS get the date format before the time. Even when I Format the cell as Time only.


Hi RonMcK,

You are definitely right about working to hard. I have to enter times from A1 - AR117. It s hard to get into a rythem if you have to keep Shift : every 2 digits. I did not see a screen shot... and I am not sure about "Have you used Format > Cells > Numbers > Time?"

I have tried both Format > Cells > Numbers and Format > Cells > Time. I am going assume that is what you ment.

[dleckie]

Hi RonMcK,

I am sure that would work by adding the delimiter ( : ).... but.... where? Under Custom? I don't see anywhere to add it besides in the Type field.

Under Time? The same as Custom but the Date is automatically added and as you pointed out that is what is throwing me off.

Thanks
Dave

[Bob Phillips]

You have to add colons to tell Excel it is time. If you want to enter numbers and get them interpreted as time, use a custom format of 00\:00\:00

[RonMcK]

Dave,

I'm playing with some VBA to see what I can create to help you. Do you need to enter unique time values in each of the cells (rows and columns) between A1 (upper left) and AR117 (lower right)? Or, do you enter times in only selected cells in that range?

Thanks,

[dleckie]

Hi RonMcK,

I enter times in selected cells. And the range can shrink or grow. It depends on the amount of testing I am doing. The times has to be entered down to the second.

I was thinking a macro that could be created when ONLY a number is found in a cell. The colon ( : ) will be entered between the digits starting from the right, move 2 characters left, enter colon, move 2 characters left, enter colon.

OR.... just a way to format the cell to accept 5 - 6 digits ( for times that are 1:12:50 or 12:13:59) and enter the colons in the appropriate locations.

Thanks for you help,
Dave

[Bob Phillips]

If you want code http://www.cpearson.com/excel/DateTimeEntry.htm

[RonMcK]

<deleted msg & incomplete macro>

Chip Pearson's solution is better. Thanks, Bob!!

Thanks,

[dleckie]

Just got out of meeting. Going to lunch. Will try when I get back. THANKS A BUNCH for all the help guys.

Dave

[RonMcK]

Dave,

To make this code work, you need to open VBE, go to Project Explorer, click on the workbook in question (if it collapsed), click on the Sheet where you will be entering data. Then, paste the code (worshkeet_change) into the code window.

Then, you need to make one small but critical edit: the applicable Range. In the code fragment below, copied from Chip's program, you need to change 'A1:A10' to the following. This code will find the last row used in the column range A:AR.


On Error GoTo EndMacro
'// Change Range() for the actual case
If Application.Intersect(Target, Range("A1:AR" & _
Cells.Find(What:="*", SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row)) Is Nothing Then
Exit Sub
End If
'// Bail out if more than 1 cell is selected
If Target.Cells.Count > 1 Then
Exit Sub
End If
'// Bail if cell is empty
If Target.Value = "" Then
Exit Sub
End If


I'll be out for several hours, I'll look back in late this afternoon.

[dleckie]

Hey Ron and all,

Ok I did the followoing steps:
1) Opened new workbook
2) Entered some numbers in cells Row A1:A10. All were 5 - 6 digits long
3) Went to Tools > Macro > VBA
4) Clicked on Sheet 1 to open up VBA Worksheet
5) Copeid all the code pertaining to Time from


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String
.
.
.
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub

6) Tried to F8 - Step thought the code but it would not work
7) Clicked Save in VBA and name it Test. Closed VBA.
8) Went to Tools > Macro > Macros and there were no macros. HUH???
9) Closed Workbook Test and opend it back up. Was asked if I wanted to enable macros, said yes, and still could not find macro under Tools > Macro > Macros
10) Went to Tools > Maroc > VBA and code it there but still can not F8 - Step thought the code.

I am a bit confused.

Thanks
Dave

[Bob Phillips]

Post the workbook, with the code that you added.

[dleckie]

Here is the woorkbook I created with the code in it. Did that work?

c:\test.xls

[Bob Phillips]

You need t go to advanced mode and Manage Attachments.

[dleckie]

Here is the workbook that I created with the code. Thanks

[Cyberdude]

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.

[Bob Phillips]

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.

[RonMcK]

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,

[dleckie]

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

[dleckie]

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

[RonMcK]

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:Sub Macro3()
'
' Macro3 Macro
' Macro recorded 9/26/2008 by Ron McKenzie
'

'
End Sub
Bob, any suggestions?

Cheers!

[RonMcK]

Dave,

Fixing the time formating is pretty easy. Add the second line (see below) and you should get you 'military time' entries.
.Value = TimeValue(TimeStr)
.NumberFormat = "h:mm:ss" 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)

[dleckie]

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

[RonMcK]

Dave,

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

Cheers!