Solved: FORMATTING A CELL TO SHOW HR & MIN

[GaryB]

Hi All,

I know this has got to be a very rudimentary question, but I am not that familiar with Excel. I tried formatting a cell to show time in hours and minutes and selected the first selection in the time list 13:30 and when I try to enter a time in the cell it shows 0:00 not matter what I enter. If I select 1:30 PM no matter what I enter it shows 12:00 AM. I know this is a real beginners dilemma, but, what do I need to do to have it show the actual time I input. I need to calculate hours worked so I need it to be able to add and subtract in this format. What am I doing wrong.

Please Help.

Thanks,

Gary

[Bob Phillips]

YOu have to enter time with the colon, such as 13:35

[GaryB]

Hi El Xid,
Thanks, it worked fine. My objective is to somehow default the colon. This is going to involve a lot of entries daily and I was hoping to be able to enter, lets say, 1200 and have it show as 12:00. I was hoping the time format would do that. Is there anyway to accomplish this in Excel? I am using office 2000 by the way, but, this is going to eventually end up on a Mac.
Gary

[lucas]

Right click the sheet tab and select view code.....paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub

'Define the range where you want the code to work
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
On Error GoTo errHandler:
With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
Select Case .Value
Case 0
.NumberFormat = "hh:mm"
Case 1 To 99
.Value = TimeSerial(0, .Value, 0)
.NumberFormat = "hh:mm"
Case 100 To 2399
.Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0)
.NumberFormat = "hh:mm"
Case 10000 To 235959
.Value = TimeSerial(Int(.Value / 10000), _
Int((.Value Mod 10000) / 100), .Value Mod 100)
.NumberFormat = "hh:mm:ss"
Case 240000 To 245959
.Value = TimeSerial(0, Int((.Value Mod 10000) / 100), .Value Mod 100)
.NumberFormat = "hh:mm:ss"
Case Else
End Select
End If
End With
errHandler:
Application.EnableEvents = True
End Sub


It is set up to work on column A so you might have to change the range.

[GaryB]

Hi Lucas,

Awesome. That did the trick. Thank you both very much for help. Hope everyone has a great holiday and let's hope for a much better tomorrow.

Gary