# Thread: Rounding Meduim Time Format

1. Grand Poobah
Join Date
May 2003
Location
Dallas
Posts
820
Provided Answers: 5

## Unanswered: Rounding Meduim Time Format

is there an existing function that rounds a time to the nearest 1/2 hour or 1/4 hour.

Say I have a control that is medium time format and it is :

12:05 PM

how can I automatically round to the next 1/4 or 1/2 hour? so that it becomes

12:15 PM or 12:30 PM

2. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513
Sorry, I responded to the wrong thread.

3. Grand Poobah
Join Date
May 2003
Location
Dallas
Posts
820
Provided Answers: 5
JerryDal - that does work for an integer field but I am dealing with a medium time format field and if I round up to the top of the hour I have to add an hour to the "H" part of the string

I spent about 20 minutes messing with it and this works - so if anyone needs it - here it is

Public Sub RoundTime()
Dim myround , myhour, newtime

myround = left(Right(Format(Time(), "Medium Time"), 5), 2)

If myround <= 15 Then
myround = 15
ElseIf myround > 15 And myround <= 30 Then
myround = 30
ElseIf myround > 30 And myround <= 45 Then
myround = 45
Else: myround = "00"
End If

If Right(left(Time(), 2), 1) = ":" Then
myhour = "0" & left(Time(), 1)
Else
myhour = left(Time(), 2)
End If
If myround = "00" Then
myhour = myhour + 1
End If
Newtime = myhour & ":" & myround & ":00" & " " & Right(Format(Time(), "Medium Time"), 2)

End Sub

4. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
here's an alternative time-roundup that i have in stock:

Code:
```Public Function RoundMinutesUp(raggedTime As Date, roundMinutes As Integer) As Date

'   /// round time upwards to the nearest roundMinutes interval
'   /// caller must verify that raggedTime is a valid Access time

Dim intMinutes As Integer

'   /// if there is a risk of unwanted seconds, uncomment the next line
'raggedTime = DateAdd("S", -Second(raggedTime), raggedTime)

intMinutes = Minute(raggedTime) Mod roundMinutes

If intMinutes > 0 Then
RoundMinutesUp = DateAdd("N", roundMinutes - intMinutes, raggedTime)
Else
RoundMinutesUp = raggedTime
End If

End Function```
izy

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•