MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Time Sheet Military Time


Posted by Ray Berry on November 18, 2001 8:22 PM

How would you write a formula to compute time worked in a 24 hr clock format to include the following
working 0-7 minutes counts zero
7-22 minutes counts as 1/4 hour
23-37 minutes as 1/2 hour
38-52 minures 3/4 hour
53-60 minutes as 1 hour
start finish hours worked
0600 1500
1800 0225

it would have to take into consideration 24 hour shifts so that it can compute starting a shift in the evenong and getting off the next morning

Thanks


Posted by Paul Akkermans on November 19, 2001 12:58 AM

Use this function:

Function Cal_Time(Stime, Etime As Variant) As Variant
Dim Ctime As Variant

If Etime - Stime < 0 Then
Ctime = 2400 - Stime + Etime
Else
Ctime = Etime - Stime
End If
Select Case Ctime Mod 100
Case 0 To 7
Cal_Time = Int(Ctime / 100)
Case 8 To 22
Cal_Time = Int(Ctime / 100) + 0.25
Case 23 To 37
Cal_Time = Int(Ctime / 100) + 0.5
Case 38 To 52
Cal_Time = Int(Ctime / 100) + 0.75
Case 53 To 60
Cal_Time = Int(Ctime / 100) + 1
End Select
End Function