Timesheets

D

dianneleigh

New member
Local time
Today, 14:59
Joined
Nov 28, 2017
Messages
9
I currently have tables set up to track employee time. TimeIn, TimeOut and Lunch. Since we round time I set it up as a general number and use military time to calculate howver, obviously, this will leave room for error.

Is there any way to enter it as a time and round it to the nearest quarter hour then subtract time in and lunch from the time out?

Thank you!
Dianne
 
dianneleigh said:
I currently have tables set up to track employee time. TimeIn, TimeOut and Lunch. Since we round time I set it up as a general number and use military time to calculate howver, obviously, this will leave room for error.

Is there any way to enter it as a time and round it to the nearest quarter hour then subtract time in and lunch from the time out?

Thank you!
Dianne
Click to expand...

Here is a function that will round the time (with Date) to the nearest quarter of an hour. It rolls over to the next day if you near midnight.

Use, e.g. RoundedDateTime = RoundToNearestQHour(#30/11/2017 16:23:40#)

You would get #30/11/2017 4:30 PM# in the RoundedDateTime variable.

Code: 
Function RoundToNearestQHour(DT As Date) As Date
    Dim rHrs As Long, rMins As Long, rDate As Long, nn As Double
    
    rDate = DateSerial(Year(DT), Month(DT), Day(DT))
    rHrs = Hour(DT)
    rMins = Minute(DT)
    

    nn = rMins / 15
    If nn - Int(nn) > 0.5 Then
      rMins = 15 * (Int(nn) + 1)
      If rMins = 60 Then
         rMins = 0
         rHrs = rHrs + 1
         If rHrs = 24 Then
            rHrs = 0
            rDate = rDate + 1
         End If
      End If
    Else
      rMins = 15 * Int(nn)
    End If
    RoundToNearestQHour = DateAdd("n", 60 * rHrs + rMins, CDate(rDate))
End Function

Best,
Jiri
 
Last edited:
hi Dianne

Excuse my ignorance but what do you mean by military time?
 
gasman why dont you share your db.
 
Hi Paul
No I don't think she means a 24 hour clock as that would be equally accurate.

Sent from my iPhone using Tapatalk
 
Last edited:
I think this is what arnelgp was inferring to?

I do this in my DB.
I am only posting the function as the DB is not working correctly

I pass in the start and end times plus any deduction for lunch
I round it to 6mins (0.1 Hour) unless it is a quarter of an hour.

I did this as I export the hours to Excel and was getting 23.3333345 sort of thing.

You already have been given one function which will likely be more robust than mine, but it suits my purposes.

Code: 
Public Const intInterval = 6 ' Rounding for hours

Public Function CalcTime(dtstart As Date, dtend As Date, Optional intDeduction As Integer)
Dim intMinutes As Long, intMod As Integer
CalcTime = DateDiff("n", dtstart, dtend) - intDeduction

' Round to 6 mins if not a 15 min interval
intMod = CalcTime Mod 60
If intMod <> 15 And intMod <> 30 And intMod <> 45 Then
    CalcTime = Round(CalcTime / intInterval, 0) * intInterval
End If

End Function
HTH
 
Last edited:
did you not try it, i already fix the jumping recird mystery.
 
Solo712 said:
Here is a function that will round the time (with Date) to the nearest quarter of an hour. It rolls over to the next day if you near midnight.

Use, e.g. RoundedDateTime = RoundToNearestQHour(#30/11/2017 16:23:40#)

You would get #30/11/2017 4:30 PM# in the RoundedDateTime variable.

Code: 
Function RoundToNearestQHour(DT As Date) As Date
    Dim rHrs As Long, rMins As Long, rDate As Long, nn As Double
    
    rDate = DateSerial(Year(DT), Month(DT), Day(DT))
    rHrs = Hour(DT)
    rMins = Minute(DT)
    

    nn = rMins / 15
    If nn - Int(nn) > 0.5 Then
      rMins = 15 * (Int(nn) + 1)
      If rMins = 60 Then
         rMins = 0
         rHrs = rHrs + 1
         If rHrs = 24 Then
            rHrs = 0
            rDate = rDate + 1
         End If
      End If
    Else
      rMins = 15 * Int(nn)
    End If
    RoundToNearestQHour = DateAdd("n", 60 * rHrs + rMins, CDate(rDate))
End Function

Best,
Jiri
Click to expand...


Just in case it's not clear: to get the net minutes worked you do

Code: 
NetMinutes = DateDiff("n", RoundToNearestQHour(StartDTStamp), _
                               RoundToNearestQHour(EndDTStamp)) - LunchMinutes

Best,
Jiri
 
Yes military time. I assume I need to use the general time format as there are many calculations that need to be made including accruals that can be up to 500 hours.

It needs to start with timein rounded to nearest quarter hour. TimeOut rounded to nearest quarter hour and lunch. Then timeout less time in and lunch.

Do you put the round code in the table or the query?
 
dianneleigh said:
Yes military time.
Click to expand...

Military time would be a format. A DATE is stored as a number where the integer part is the actual date and the decimal is the time.

If not formatted, you should see something like 45223.3354 for something you would format to be "30-NOV-17 15:00". Please do not confuse the format with how an SQL backend or Access will store the value.

As such you have no need to format specifically to a "Military" time. It also matters not how others format the date/time entered. Access will convert to a number.
 
Thank you all for your help above. Thanks to you I finally got it to round time and calculate the daily total then break the time into categories.

I can't get it to total the week on the form or total the category times as well as convert 1/2 an hour to .5. I was working on the Copy Of frmTimeCardsMain form.

Link to db
drive.google.com/open?id=1AcdIkYcUwivbfb1xXiSurPY7rBhlg3oB
 
You must log in or register to reply here.

Similar threads

MattBaldry
Activity Timing
2
Replies
22
Views
601
MattBaldry
MattBaldry
C
Conditional Format Issue
Replies
6
Views
153
DHookom
DHookom
D
Keeping track of timing
Replies
18
Views
428
ebs17
E
zelarra821
Solved Subtract hours
2
Replies
31
Views
1,098
Pat Hartman
P
C
Assessing Project Size
2
Replies
33
Views
1,925
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom