Do I have to use : to seperate hours and minutes?

**Jeff Manning** · 03-15-2005, 09:38 AM

Hi,
I need to input mins and seconds to cells and do not want to have to press : every time to seperate them.
Is there a way of making cells assume that I am going to input a 4 digit time (mins and secs) every time and getting an automatic :
Or
Is there a way of making the . on the numbers on the right hand side of the keyboard do :
Thanks
Jeff

**swatsp0p** · 03-15-2005, 10:41 AM

As long as you will not be doing any time calculations on these entries, use Format>Cells>Number>Custom option and enter ##":"##

keying in 1245 will result in 12:45 being displayed, keying in 137 will result in 1:37.

Note, however that these are not true TIME serial numbers and cannot be used to calculate times.

HTH

**Jeff Manning** · 03-15-2005, 11:55 AM

Thanks for the reply.
That does indeed work as you say, but I am converting the time to seconds in the next column, so the values are wrong on the calculation. I assume I will have to input the values in the longhand way to get the calculation to work.
Regards

**swatsp0p** · 03-15-2005, 12:46 PM

OK, I found this macro that, when placed in the Workbook>sheetchange should accomplish what you need. You MUST format your cells as Custom>mm:ss or it will display the true time as entered (e.g. 23:45 will show as 12:23:45 AM).

With this, you only need to enter active digits (no leading zeros required). Therefore, entering '1' will return 00:01, '12' will be 00:12, '123' will be 01:23 and '1234' will show as 12:34

---------------------------------------------------
NOTE: I did not write this code, but I don't have any documentation as to where this came from. My apologies and thanks to the author of this code. I did make some minor modifications to meet the OPs needs....
---------------------------------------------------

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)

Dim TimeStr As String

On Error GoTo EndMacro
' ADJUST THE 'RANGE' BELOW TO MEET YOUR NEEDS -- THIS MACRO WILL
' ONLY CONVERT TO TIME IN THE SPECIFIED CELL RANGE
If Application.Intersect(Target, Range("A1:AZ10000")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:00:01 AM
TimeStr = "00:00:0" & .Value
Case 2 ' e.g., 12 = 00:00:12 AM
TimeStr = "00:00:" & .Value
Case 3 ' e.g., 735 = 00:07:35 AM
TimeStr = "00:0" & Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 00:12:34
TimeStr = "00:" & Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True

Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
ActiveCell.Offset(-1, 0).Select
End Sub

----------------------------------------------------

Bruce

**neopolitan** · 03-18-2005, 09:46 AM

Bruce -
I also needed this code but can't get it to work. I installed in the This Workbook code window and it is in effect as I notice a change in the affected cells. I did revise the cell references to fit my sheet and I did format cells Custom>mm:ss.

The result I get no matter what I enter (645 & 1545) is either 0:00 or 00:00. When I look in the formula window what is in the cell is "10/6/1901 12:00:00 AM" for the input of "645". I was hoping for "6:45".

Can you help? Thanks.

**swatsp0p** · 03-18-2005, 10:38 AM

Hi, Neo: For your cell to return 10/6/1901 12:00:00 AM, tells me the cell is REALLY formatted as DATE (645 is the code for that date). Re-verify your formatting!

Once you change the formatting, you will need to reenter the times, as the macro (as written) only works on "SheetChange" in each cell. It will NOT change times *already* entered in the cells.

HTH

**neopolitan** · 03-18-2005, 01:06 PM

Okay, I confess - I had the code in the wrong module. It is now in the This Workbook code sheet and it does convert the raw numbers to hour:minute format. Thanks.