Access function to round time to quarter hour

[bdmoran]

Hello,

I have a report in access that has a list of times employees clocked out of their shift. I need to round the time they clocked out to the nearest quarter hour but expressed as a decimal.

for example, I need anything less than <=3:59=4.00 anything >=4:15=4.25 etc.

Any help would be appreciated!

 

[MajP]

There are lots of ways to round, but that example does not make sense. Are you rounding up or down or some combination of both.

To clarify answer what you want for the following values.

3:01
3:07:29
3:07:31
3:14
3:16

 

[bdmoran]

3:01 = 3.25 HOURS WORKED
3:07:29 = 3.25 HOURS WORKED
3:07:31 = 3.25 HOURS WORKED
3:14 = 3.25 HOURS WORKED
3:16 = 3.25 HOURS WORKED

Anything time from 4:16-4:30 would = 4.50 HOURS WORKED.

Does this clarify what I want?

 

[PWise]

if
Anything time from 4:16-4:30 would = 4.50 HOURS WORKED.

then why
3:16 = 3.25 HOURS WORKED

 

[MajP]

Public Function roundTime(dtm As Date) As String
  'Your example still makes no sense I will assume
  'you always round up to next .25 hour
  Dim tempTime As Date
  tempTime = dtm - Int(dtm)
  If (tempTime * 96) / 96 = Fix(tempTime * 96) / 96 Then
    tempTime = (tempTime * 96) / 96
  Else
    tempTime = Fix(tempTime * 96) / 96 + (15 / (24 * 60))
  End If
  roundTime = Format(tempTime, "hh.mm")
End Function

verification

Public Sub test2()
  Debug.Print roundTime(#3:00:00 AM#)
  Debug.Print roundTime(#3:01:00 AM#)
  Debug.Print roundTime(#3:14:00 AM#)
  Debug.Print roundTime(#3:15:00 AM#)
  Debug.Print roundTime(#3:16:00 AM#)
  Debug.Print roundTime(#3:29:00 AM#)
  Debug.Print roundTime(#3:31:00 AM#)
  Debug.Print roundTime(#3:44:00 AM#)
  Debug.Print roundTime(#3:45:00 AM#)
  Debug.Print roundTime(#3:46:00 AM#)
  Debug.Print roundTime(#3:59:00 AM#)
End Sub

03.00
03.15
03.15
03.15
03.30
03.30
03.45
03.45
03.45
04.00
04.00

 

[bdmoran]

Sorry I am confusing you. I made a mistake.
This is the increments I need to round up to:

3-3:15=4.00
3:16-3:29=4.25
3:30-3:45=4.50
3:46-3:59=4.75
4-4:15= 4.00
And so on.....

So for example,say I worked from 11:00-3:00 and I clock out at 3:10. For payroll purposes, I need 3:10 to =4.00. Meaning that I worked 4 hours and I won't get paid for another .25 hours UNLESS I worked until atleast 3:16. Is that any better?

 

[dhookom]

Now, I am even more confused. How can this be correct:

3-3:15=4.00
...
4-4:15= 4.00

Duane
Hook'D on Access
MS Access MVP

 

[bdmoran]

Because.....it doesnt matter what the time is. For any time...3:15, 4:15, 5:15, 6:15 they will all be equal to just one hour. I used the 4.00 as a reference for working 4 hours. (This is just what I had written down, sorry it was confusing) So just look at my example....if i work 2:00-3:15 than I need the result to be 1.00 as in 1 hour worked. If I work 2:00-6:15 then I need the result to be 4.00 as in 4 hours worked. If I work 2:00-6:33 I need a result of 4.50. So that means I worked enough over time to get paid the extra .50.

 

[MajP]

Take a deep breath and think before posting.

Public Function roundTime(dtmStart As Date, dtmEnd As Date) As String
  Dim tempTime As Date
  tempTime = dtmEnd - dtmStart
  If Abs(Fix(tempTime * 96) / 96 - (tempTime * 96) / 96) < 0.0001 Then
    tempTime = Fix(tempTime * 96) / 96 - (15 / (24 * 60))
  Else
    tempTime = Fix(tempTime * 96) / 96
  End If
  roundTime = Format(tempTime, "hh.mm")
End Function

Public Sub test2()
  Debug.Print roundTime(#11:00:00 AM#, #3:00:00 PM#)
  Debug.Print roundTime(#11:00:00 AM#, #3:01:00 PM#)
  Debug.Print roundTime(#11:00:00 AM#, #3:15:00 PM#)
  Debug.Print roundTime(#11:00:00 AM#, #3:16:00 PM#)
  Debug.Print roundTime(#11:00:00 AM#, #3:29:00 PM#)
  Debug.Print roundTime(#11:00:00 AM#, #3:30:00 PM#)
  Debug.Print roundTime(#11:00:00 AM#, #3:31:00 PM#)
End Sub

results

03.45
04.00
04.00
04.15
04.15
04.15
04.30

 

[MajP]

oops. Let me fix. You need decimal hours. wait one.

 

[bdmoran]

Sorry I am new to all of this stuff. I have one last question. Is there any way I can have the results end in .00, .25, .50 or .75 instead of .15, .30, etc.

This is the way that payroll has it formatted now and it would be best to keep it that way.

Thanks so much again for your help!

 

[MajP]

Public Function roundTime(dtmStart As Date, dtmEnd As Date) As String
  Dim tempTime As Date
  tempTime = dtmEnd - dtmStart
  If Abs(Fix(tempTime * 96) / 96 - (tempTime * 96) / 96) < 0.0001 Then
    tempTime = Fix(tempTime * 96) / 96 - (15 / (24 * 60))
  Else
    tempTime = Fix(tempTime * 96) / 96
  End If
  roundTime = Format(tempTime, "hh.mm")
  roundTime = Replace(roundTime, ".15", ".25")
  roundTime = Replace(roundTime, ".30", ".50")
  roundTime = Replace(roundTime, ".45", ".75")
End Function

Public Sub test2()
  Debug.Print roundTime(#11:00:00 AM#, #3:00:00 PM#)
  Debug.Print roundTime(#11:00:00 AM#, #3:01:00 PM#)
  Debug.Print roundTime(#11:00:00 AM#, #3:15:00 PM#)
  Debug.Print roundTime(#11:00:00 AM#, #3:16:00 PM#)
  Debug.Print roundTime(#11:00:00 AM#, #3:29:00 PM#)
  Debug.Print roundTime(#11:00:00 AM#, #3:30:00 PM#)
  Debug.Print roundTime(#11:00:00 AM#, #3:31:00 PM#)
  Debug.Print roundTime(#2:00:00 PM#, #3:15:00 PM#)
  Debug.Print roundTime(#2:00:00 PM#, #6:15:00 PM#)
  Debug.Print roundTime(#2:00:00 PM#, #6:33:00 PM#)
End Sub

[/code]
03.75
04.00
04.00
04.25
04.25
04.25
04.50
01.00
04.00
04.50
[/code]

 

[bdmoran]

The report I am working with is in Access? How would I go about using this code in Access?

 

[MazeWorX]

how do i get a job there lol

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[MajP]

assuming you have a fields in your table that are date time fields for clocking in and clocking out, you could use it as the recordsource for you report.

Select someField1, someField2, .... roundTime(someTimeInField,SomeTimeOutField) as Hours_Worked from someTable

Or it can be used in a calculated field by passing in field names

=roundTime(SomeTimeInField,SomeTimeOutField)

 

[bdmoran]

In my report I have a calculated field that is the number of hours an employee worked. This field is called ACTUAL. Could you show me an example of a function that rounds this "Actual" field? So it is the same as I wanted before, just without calculating the hours worked.


MajP Thank you so much for your time and patience. Greatly appreciated!

 

[MajP]

Is Actual a numeric value or date value?
What calculation do you use?

assuming it is an date value then

Public Function roundTime2(elapsedTime As Variant) As String
  On Error Resume Next
  If IsDate(elapsedTime) Then
    Dim tempTime As Date
    tempTime = elapsedTime - Int(elapsedTime)
    If Abs(Fix(tempTime * 96) / 96 - (tempTime * 96) / 96) < 0.0001 Then
      tempTime = Fix(tempTime * 96) / 96 - (15 / (24 * 60))
    Else
      tempTime = Fix(tempTime * 96) / 96
    End If
    roundTime2 = Format(tempTime, "hh.mm")
    roundTime2 = Replace(roundTime2, ".15", ".25")
    roundTime2 = Replace(roundTime2, ".30", ".50")
    roundTime2 = Replace(roundTime2, ".45", ".75")
  End If
End Function

Public Sub test3()
  Debug.Print roundTime2(#4:00:00 AM#)
  Debug.Print roundTime2(#4:01:00 AM#)
  Debug.Print roundTime2(#4:14:00 AM#)
  Debug.Print roundTime2(#4:15:00 AM#)
  Debug.Print roundTime2(#4:16:00 AM#)
End Sub

03.75
04.00
04.00
04.00
04.25

If the control is calculted you cannot pass a calculted control value. You have wrap you calculation with the function.

assuming your current control source is : = SomeCalculation
Then wrap the calculation: =roundTime2(someCalculation)

 

[bdmoran]

Nope Actual is a numeric value.

For example, I have 3 fields right now. In time, out time, and actual.

Say that IN time =11:45 and OUT time is 15:23. I set up a query to calculate how many hours the employee worked and this field is called ACTUAL. So in my example, the employee worked 11:45-15:23 which results in an ACTUAL time of 3:38. Now i want this 3:38 to round up to the nearest quater hour but in decimals (like we have been discussing).

 

[MajP]

No, the value 3:38 is either a date value (formatted to short time) or its a string. There is no such numeric value of 3:38. Numerics are integers, longs, doubles, singles, decimals, etc. If this value is a date value then the function should work as described. If the calculation returns a string then add a line to support that.
....
Dim tempTime As Date
elapsedTime = CDate(elapsedTime)
tempTime = elapsedTime - Int(elapsedTime)
...

If you are doing the calculation in the query to get elapsed time then add this function to you query and wrap the calculation.

 

[bdmoran]

Ok so I created the function as a Module in access. However, I am getting.25 for all of my results and I am not sure why.