Please help with Time Rounding

[The_Doc_Man]

I do not want to round up if the employee punches in at 5:30:01, that is still considered punching in at 5:30, it is only when they punch in at 5:31:00 or greater that I want to round up.

You are rounding non-uniformly here. Time 5:30:01 and a 15-minute interval would round to the same as would 5:31:00 and a 15-minute interval - namely 5:30:00 for both - because both times are between 5:30:00 and 5:37:30, the latter time being the time after which a 15-minute rounding interval would start rounding up. BOTH of those times would round down in the classic sense of rounding.

Do you want 5:30:30 to round UP to 5:31:00 or does any time before 5:30:59 still count as 5:30:00? (Which means the first adjustment would be truncation, not rounding.)

Does this only count for specific times like 5:30, 9:30, etc. or do you have two-stage adjustments at all times?

This is beginning to sound like you need to more completely define your requirements for us because that two-stage adjustment is not something that would so easily be applied. You might do better to use functions like DatePart to get the minutes and use a case statement to decide what time to use. In this snippet, I have not DIM'd anything.

lMins = DatePart( "n", InputDate )
lHrs = DatePart( "h", InputDate )
Select Case lMins
    Case 0
' do nothing
    Case 1 to 14
        lMins = 15
    Case 15
'do nothing
    Case 16 to 29
        lMins = 30
    Case 30
'do nothing
    Case 31-44
        lMins = 45
    Case 45
'do nothing
    Case 46-59
        lMins = 0
        lHrs = lHrs + 1
End Select
strTime = Format( lHrs, "00" ) & ":" & Format( lMins, "00" )

This is AIR code, untested, but gives the general idea of how you approach it. Note that there is a potential time / date issue for times later in the day than 23:45:00

There are other ways to write this but I chose a somewhat pedantic style to clarify what is actually going on. And this would of course involve taking apart the actual time and putting it back together based on YOUR rounding rules.

EDITED: Damned spell check doesn't like SNIPPET and thinks I meant SNIPPED!

 

[slharman1]

You are rounding non-uniformly here. Time 5:30:01 and a 15-minute interval would round to the same as would 5:31:00 and a 15-minute interval - namely 5:30:00 for both - because both times are between 5:30:00 and 5:37:30, the latter time being the time after which a 15-minute rounding interval would start rounding up. BOTH of those times would round down in the classic sense of rounding.

Do you want 5:30:30 to round UP to 5:31:00 or does any time before 5:30:59 still count as 5:30:00? (Which means the first adjustment would be truncation, not rounding.)

Does this only count for specific times like 5:30, 9:30, etc. or do you have two-stage adjustments at all times?

This is beginning to sound like you need to more completely define your requirements for us because that two-stage adjustment is not something that would so easily be applied. You might do better to use functions like DatePart to get the minutes and use a case statement to decide what time to use. In this snippet, I have not DIM'd anything.

lMins = DatePart( "n", InputDate )
lHrs = DatePart( "h", InputDate )
Select Case lMins
    Case 0
' do nothing
    Case 1 to 14
        lMins = 15
    Case 15
'do nothing
    Case 16 to 29
        lMins = 30
    Case 30
'do nothing
    Case 31-44
        lMins = 45
    Case 45
'do nothing
    Case 46-59
        lMins = 0
        lHrs = lHrs + 1
End Select
strTime = Format( lHrs, "00" ) & ":" & Format( lMins, "00" )

This is AIR code, untested, but gives the general idea of how you approach it. Note that there is a potential time / date issue for times later in the day than 23:45:00

There are other ways to write this but I chose a somewhat pedantic style to clarify what is actually going on. And this would of course involve taking apart the actual time and putting it back together based on YOUR rounding rules.

EDITED: Damned spell check doesn't like SNIPPET and thinks I meant SNIPPED!

Thank you, I will check into it, but I have solved my issue using minute() to test for 0,15,30, & 45. So far so good.
thanks

 

[slharman1]

Thank you all for helping a beginner in date/time calculations, i am marking this solved for now until further testing.

 

[MajP]

This should work too.

Public Function RoundUp15(MyTime As Date) As Date
 RoundUp15 = TimeSerial(Hour(MyTime), -Int(-Minute(MyTime) / 15) * 15, 0)
End Function

 

[MajP]

FYI. Access does not have a built in Ceiling function like Excel. You can fake it by taking the INT of the negative which gives the next lower negative. Then make that a negative
x = 2.3
-Int(-x) = 3