Timesheets (1 Viewer)

D

dianneleigh

New member
Local time
Today, 10:47
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
 
Solo712

Solo712

Registered User.
Local time
Today, 10:47
Joined
Oct 19, 2012
Messages
828
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:
isladogs

isladogs

MVP / VIP
Local time
Today, 15:47
Joined
Jan 14, 2017
Messages
18,209
hi Dianne

Excuse my ignorance but what do you mean by military time?
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:47
Joined
May 7, 2009
Messages
19,231
gasman why dont you share your db.
 
isladogs

isladogs

MVP / VIP
Local time
Today, 15:47
Joined
Jan 14, 2017
Messages
18,209
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:
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 15:47
Joined
Sep 21, 2011
Messages
14,223
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:
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:47
Joined
May 7, 2009
Messages
19,231
did you not try it, i already fix the jumping recird mystery.
 
Solo712

Solo712

Registered User.
Local time
Today, 10:47
Joined
Oct 19, 2012
Messages
828
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
 
D

dianneleigh

New member
Local time
Today, 10:47
Joined
Nov 28, 2017
Messages
9
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?
 
M

Mark_

Longboard on the internet
Local time
Today, 07:47
Joined
Sep 12, 2017
Messages
2,111
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.
 
D

dianneleigh

New member
Local time
Today, 10:47
Joined
Nov 28, 2017
Messages
9
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

zelarra821
Solved Subtract hours
2
Replies
31
Views
895
Pat Hartman
P
P
Solved Seat license security
2
Replies
25
Views
1,359
Pat Hartman
P
A
Solved Consolidate Employee Time In/Out take time to load.
Replies
3
Views
218
atzdgreat
A
R
Solved Importing and calculating the data
Replies
12
Views
491
Robrobby
R
S
ChildCarePlan
Replies
14
Views
944
silentwolf
S

Users who are viewing this thread

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