Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / January 2005

Tip: Looking for answers? Try searching our database.

Docking Time in an Excel TimeSheet

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
gsering - 31 Jan 2005 18:45 GMT
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 - 31 Jan 2005 20:41 GMT
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.

Signature

HTH

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

> Hello,
>
[quoted text clipped - 7 lines]
>
> Glenn
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.