Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    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
    Dale Houston, TX

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Sorry, I responded to the wrong thread.

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    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
    Dale Houston, TX

  4. #4
    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
    currently using SS 2008R2

Posting Permissions

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