Adjusting just the minutes of a date/time field

[phillib5]

Does anyone know a way to adjust just the minute part of a time field. For example, I need to round the minutes off for certain time intervals. If the time was 5:01:00 AM, I would need it changed to 5:00:00 AM. Or 5:39:00 PM would need to be changed to 5:45:00 PM.

This is the schema I go by:
Minute Start Minute End Round to Minute
1 7 0
8 23 15
23 37 30
38 52 45
53 60 0

Any help would be greatly appreciated. Thanks!

-Bryant

 

[Golom]

Dim Mn As Integer
Dim NT As Date
Mn = Minutes (DateField)
Select Case Mn
   Case 1 to 7:       NT = DateAdd ("n",  -Mn,DateField)
   Case 8 to 23:      NT = DateAdd ("n",15-Mn,DateField)
   Case 24 to 37:     NT = DateAdd ("n",30-Mn,DateField)
   Case 38 to 52:     NT = DateAdd ("n",45-Mn,DateField)
   Case 53 to 60:     NT = DateAdd ("n",60-Mn,DateField)
End Select

I changed 23-37 to 24-37 since "23" should not occur in more than one range.

 

[dhookom]

You could try a user defined function like:

Function RoundTime(pdatTime As Date, intMinutes As Integer) As Date
    'multiply by 24 * (60/intMinutes)
    'round using clng and then divide by 24 * (60/intMinutes)
    Dim lngTime As Long
    lngTime = pdatTime * 1440 / intMinutes
    RoundTime = CDate(lngTime / (1440 / intMinutes))
End Function

To round to the nearest 15 minute interval, you would call this like:
RoundTime([TimeField],15)



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[phillib5]

Awesome, I used the latter solution and it worked like a charm. Both solutions were great, thank you.