Docking Time in an Excel TimeSheet

[gsering]

Hello,

I have a timesheet spreadsheet that calculates time in / out. Right
now I use the MRound function to round up or down to the nearest 1/4
hour. Our policy says that if you are more than 5 minutes late, you
will be docked up to the 15 minutes. Is there any good way to make
this into a formula so if I enter the actual time clocked in as say,
8:06am, it will calculate the "clocked time in" as 8:15am?
Thanks,

Glenn

 

[Bob Phillips]

Glenn,

This code should do it. But I have to say that seems a dumb policy to me, I
wouldn't be surprised if people are deliberately 4 minutes late because of
it, and if they are 6 minutes late, they would wait until they are 19
minutes late to not be taken advantage of.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
If .Value - Int(.Value * 24 * 4) / 24 / 4 > (1 / 24 / 12) Then
.Value = Int(.Value * 24 * 4) / 24 / 4 + (1 / 24 / 4)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)