Sleeper: [h]:mm conversion

[cmpgeek]

hey yall,
i have a stime sheet set up where i log all time activity and estimate how much time off someone will have months down the road, etc. i got an awesome piece of coding from the KB here and adjusted it to fit my needs and all has gone fairly well. but there is something i have noticed and i cant figure out #1 why Excel feels the need to do this, and #2 how to get it to stop doing it...
here is the coding:

Option Explicit

Sub MyWsChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("H:U")) Is Nothing Then Exit Sub
On Error GoTo errHandler:
With Target
        If IsNumeric(.Value) Then
            Application.EnableEvents = False
            Select Case .Value
            Case 0
                .NumberFormat = "[h]:mm"
            Case 1 To 99
                .Value = TimeSerial(0, .Value, 0)
                .NumberFormat = "[h]:mm"
            Case 100 To 9999
                .Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0)
                .NumberFormat = "[h]:mm"
            Case Else
            End Select
        End If
    End With
errHandler:
    Application.EnableEvents = True
End Sub

99.9% of the time i dont have to worry about a thing, but once the hours get so high Excel starts changing things. For example: when i type in 8000 it is supposed to change it so 80:00 to signify <sp?> 80 hours (a typical pay period for a full time employee). However, when i hit enter, instead of changing it to 80:00 it changes it to 0:03. i have also noticed that when i have a formula set up to add several cells together that it will sometimes do this same thing. In those instances, not only does it convert the number this way, but it also replaces the formula with a date and time value.

Why does Excel do this, and is there anyway to get around it?

thanks yall!

[johnske]

Hi nomi,

By entering 8000, I assume you mean you're actually entering 80 hours and 00 minutes...

The main problem is that your cells are being formatted in date/time format and, when you add dates and time you may get some strange looking results.

When you're at the part where you need to add the accumulated hours and minutes you need something like the following to do that...

Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Cells.Count > 1 Then Exit Sub
      If Intersect(Target, Range("H:U")) Is Nothing Then Exit Sub
      Target.NumberFormat = "#0"":""00"
      If Right(Target, 2) >= 60 Then Target = Target + 40
End Sub

HTH
John

[cmpgeek]

thanks for the suggestion - i wont be back at work until MOnday but i will try it then...

[johnske]

OK, but mind - what I gave above's only the format...

You'd need to do something else like this below (note that this's far from ideal, it assumes you're going to keep a permanent record of the accumulated hours worked, and that you make your entries without mistakes...i.e. if you enter a time, then go back and change it later, the "previous" time remains added to the total, so you need to clear the contents (for the sum) then go back and re-enter everything on that row)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim hrs%, mins%
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("H:U")) Is Nothing Then Exit Sub
'//format cells
Target.NumberFormat = "#0"":""00"
'//correct entry errors
If Right(Target, 2) >= 60 Then
MsgBox Right(Target, 2) & " minutes? There's only 00 to 59 " & _
"minutes allowed here"
Exit Sub
End If
hrs = (Target - Right(Target, 2)) / 100
mins = Right(Target, 2)
'//save & sum the total hours - used columns 3,4,5 here
'//change to wherever u want the total to appear
Columns(3).Rows(ActiveCell.Row) = Columns(3). _
Rows(ActiveCell.Row) + hrs
'//save & sum the total minutes
Columns(4).Rows(ActiveCell.Row) = Columns(4). _
Rows(ActiveCell.Row) + mins
'//if there are more than 60 minutes showing in the 'minutes' cell
Do While Columns(4).Rows(ActiveCell.Row) >= 60
'subtract 60 mins from the total minutes
Columns(4).Rows(ActiveCell.Row) = Columns(4). _
Rows(ActiveCell.Row) - 60
'add an hour to the total hours
Columns(3).Rows(ActiveCell.Row) = Columns(3). _
Rows(ActiveCell.Row) + 1
Loop
'//show the total hrs and mins
Columns(5).Rows(ActiveCell.Row) = Columns(3). _
Rows(ActiveCell.Row) & ":" & Columns(4).Rows(ActiveCell.Row)
End Sub