Time calculation question

[Jmcb1b]

I am using a spreadsheet to calculate the total amount of hours worked.
The time is recorded in my cells as military time. The total time is
then figured in the cell labeled "total hours".

example:

work begin | lunch start | lunch finish | work end |
total hours | excess time
0715 _______ 1130 ______ 1200 ______ 1600 ______ 0800 _____
????

I need the total hours not to exceed 0800.
If total hours exceeds 0800 then enter 0800 in total hours cell and
place excess time (overtime) in a seperate cell labeled excess time.

Also, is there a way to enter the military time, as above, and have the
total hours displayed as 8.00? (this seems to be impossible to do)

Any help is greatly appreciated. Thanks in advance.

 

[Earl Kiosterud]

Jmcb1b,

The total time up to 08:00 would be in another cell:

=MIN((B2-A2) + (D2-C2), TIMEVALUE("08:00"))

Time over 8 hours:

=MAX((B2-A2) + (D2-C2)- TIMEVALUE("08:00"),0)


Formatted as necessary. Some parentheses not necessary, but are for
clarity.

 

[Kieran]

total hours is =MIN(F4-E4+D4-C4,8/24)
excess hours is =IF(F4-E4+D4-C4-G4>0, F4-E4+D4-C4-G4, 0)


Regarding the display of military hours the follwoing worksheet even
macro will massage a number such as 1230 to 12:30.

It will meed some tweaking to idenitfy the correct input ranges a
identifed in the Set InputRange = Union(... statement block

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim TimeStr
As String
Dim InputRange
As Range
Dim cellValue
As String
Dim SecondsInDay
As Long
Dim SecondsSince1200
As Long
Dim Hours
As Long
Dim Minutes
As Long
Dim SecondsInHour
As Integer
Set InputRange = Union( _
Range("g44:g47,d44:d47,j44:j47,M44:M47"), _
Range("f45:f47,i45:i47,l45:l47,O45:O47"), _
Range("F9:F11,G8:G11,I9:I11,L9:L11"), _

Range("G17:G20,I18:I20,G26:G29,I27:I29,G35:G38,I36:I38,J35:J38,J26:J29,J17:J20")
_

Range("J8:J11,O9:O11,M8:M11,M17:M20,O18:O20,M26:M29,O27:O29,M35:M38,O36:O38,D811")
_

Range("D1720,D2629,D3538,F36:F38,F27:F29,F18:F20"), _

Range("F9:F11,I9:I11,L9:L11,O9:O11,F18:F20,I18:I20"), _

Range("L18:L20,O18:O20,F27:F29,I27:I29,L27:L29,O27:O29"), _

Range("F36:F38,I36:I38,L36:L38,O36:O38,F45:F47,I45:I47,L45:L48,L45:L47,O45:O47"))

SecondsInDay = 24 * CLng(3600)
SecondsInHour = 3600

On Error GoTo EndMacro

If Application.Intersect(Target, InputRange) Is Nothing Then
Exit Sub
End If

If Target.Cells.Count > 1 Then
Target.Cells(1, 1).Select
Target.Cells(1, 1).Activate
MsgBox "Error - You cannot select more than one cell fo
entry." & vbCrLf & "Please select only one cell and re-enter a tim
between 0(0:00) and 2359(23:59)", _
vbOKOnly, "Too many cells selected"
Target.Cells(1, 1).Value = ""
Exit Sub
End If

Application.ScreenUpdating = False
ActiveSheet.Unprotect

With Target
If .Value < 1 Then ' we probably have a real time
If IsNumeric(.Value) = True Then ' convert to a tim
string
SecondsSince1200 = SecondsInDay * .Value
Hours = Int(SecondsSince1200 / SecondsInHour)
Minutes = Int((SecondsSince1200 - (Hours
SecondsInHour)) / CLng(60))
TimeStr = Hours & ":" & Minutes
End If ' time wa
entered as a time
Else ' process as a string
cellValue
Trim(PeriodChange.TrimNonNumerics(Target.Value)) ' clean up value i
string
If .HasFormula = False Then
' format time based on number length
Select Case Len(cellValue)
Case 1 ' e.g., 1 = 00:0
AM
TimeStr = "00:0" & cellValue
Case 2 ' e.g., 12
00:12 AM
TimeStr = "00:" & cellValue
Case 3 ' e.g., 735
7:35 AM
TimeStr = Left(cellValue, 1) & ":"
Right(cellValue, 2)
Case 4 ' e.g., 1234
12:34
TimeStr = Left(cellValue, 2) & ":"
Right(cellValue, 2)
Case 5 ' e.g., 12345
1:23:45 NOT 12:03:45
TimeStr = Left(cellValue, 1) & ":"
Mid(cellValue, 2, 2) & ":" & Right(cellValue, 2)
Case 6 ' e.g., 123456
12:34:56
TimeStr = Left(cellValue, 2) & ":"
Mid(cellValue, 3, 2) & ":" & Right(cellValue, 2)
Case Else
MsgBox "The time entered " & cellValue &
cannot be recognised." & vbCrLf & _
"Please re-enter", vbOKOnly
vbInformation, "Invalid Time Entered"
.Value = ""
Exit Sub
End Select
End If

Application.EnableEvents = False ' the chang
causes yet another change event
.Value = TimeValue(TimeStr)
Application.EnableEvents = True

End If ' value is 0 t
2400

End With

ActiveSheet.Protect userinterfaceonly:=True
Application.ScreenUpdating = True
Exit Sub

EndMacro:
Application.ScreenUpdating = True
Target.Activate
MsgBox "Error - You did not enter a valid time: " & Target.Value &
vbCrLf _
& "Please re-enter a time between 0(0:00) and 2359(23:59)",
vbOKOnly, "Incorrect time entry"
Target.Value = ""
ActiveSheet.Protect userinterfaceonly:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Application.EnableEvents = True

End Sub