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