P: 7

I have a Start and End time in a table which I find the total time by using
diff: (DateDiff("n",[Start],[End]))
I then want to round this figure up to the nearest 15mins (billing period).
I tried
rounddif: CInt([diff]/15)*15
but it rounds up or down to the nearest 15. I want to force it to always round up.
eg. 6min would round to 15, 33mins would round to 45, etc
Any help would be greatly appreciated.
I am using MS Access 2007
 
Share this Question
Expert 2.5K+
P: 2,653

I have a Start and End time in a table which I find the total time by using
diff: (DateDiff("n",[Start],[End]))
I then want to round this figure up to the nearest 15mins (billing period).
I tried
rounddif: CInt([diff]/15)*15
but it rounds up or down to the nearest 15. I want to force it to always round up.
eg. 6min would round to 15, 33mins would round to 45, etc
Any help would be greatly appreciated.
I am using MS Access 2007
Hi, tmdan.
Try this
rounddif: Round([diff]/15)*15
 
P: 7

Hi, thanks for that but it still returns the same result.
Any other ideas?
  Expert 5K+
P: 8,652

Hi, thanks for that but it still returns the same result.
Any other ideas?
This little Function will perform the proper 'Round Up at 15 min. Intervals' for a differential of up to 2 hours:  Public Function fRoundUpTo15(MinDiff As Integer) As Integer

Select Case MinDiff

Case 1 To 15

fRoundUpTo15 = 15

Case 16 To 30

fRoundUpTo15 = 30

Case 31 To 45

fRoundUpTo15 = 45

Case 46 To 60

fRoundUpTo15 = 60

Case 61 To 75

fRoundUpTo15 = 75

Case 76 To 90

fRoundUpTo15 = 90

Case 91 To 105

fRoundUpTo15 = 105

Case 106 To 120

fRoundUpTo15 = 120

End Select

End Function
 "A time difference of 63 minutes Rounds Up to: " & fRoundUpTo15(63) & " minutes"
OUTPUT:  A time difference of 63 minutes Rounds Up to: 75 minutes
  Expert 5K+
P: 8,652

Hi, thanks for that but it still returns the same result.
Any other ideas?
Actually, I like my 2nd idea better:  Dim diff As Integer, diffRoundUp As Integer


If diff Mod 15 = 0 Then

diffRoundUp = diff

Else

diffRoundUp = diff + (15  (diff Mod 15))

End If
 
P: 7
 Actually, I like my 2nd idea better:  Dim diff As Integer, diffRoundUp As Integer


If diff Mod 15 = 0 Then

diffRoundUp = diff

Else

diffRoundUp = diff + (15  (diff Mod 15))

End If
Sorry, I'm a bit of a novice with VB so I was trying to acheive this with a query.
I have a text box on my form called diff with =(DateDiff("n",[Start],[End])) as the control source.
I have created a second text box called diffRoundUp but where do I put this code so that this box displays the rounded figure?
Thanks again for your help
  Expert 5K+
P: 8,652

Sorry, I'm a bit of a novice with VB so I was trying to acheive this with a query.
I have a text box on my form called diff with =(DateDiff("n",[Start],[End])) as the control source.
I have created a second text box called diffRoundUp but where do I put this code so that this box displays the rounded figure?
Thanks again for your help
Placing this code in your Form's Current() Event should do the trick:[  Private Sub Form_Current()

If Not Me.NewRecord And Not IsNull(Me![Start]) And Not IsNull(Me![End]) Then '*

Dim intMinDiff As Integer


intMinDiff = DateDiff("n", Me![Start], Me![End])


If intMinDiff Mod 15 = 0 Then

Me![diffRoundUp] = intMinDiff

Else

Me![diffRoundUp] = intMinDiff + (15  (intMinDiff Mod 15))

End If

Else

Me![diffRoundUp] = Null

End If

End Sub
  Expert 2.5K+
P: 2,653

Hi, tmdan.
You may advantageously use Int() function that always rounds down.
rounddif: Int([diff]/15)*15
 
P: 7

Hi, tmdan.
You may advantageously use Int() function that always rounds down.
rounddif: Int([diff]/15)*15
Thanks, but I want to always round up. Do you know of a variation of it to go up?
 
P: 7
 Placing this code in your Form's Current() Event should do the trick:[  Private Sub Form_Current()

If Not Me.NewRecord And Not IsNull(Me![Start]) And Not IsNull(Me![End]) Then '*

Dim intMinDiff As Integer


intMinDiff = DateDiff("n", Me![Start], Me![End])


If intMinDiff Mod 15 = 0 Then

Me![diffRoundUp] = intMinDiff

Else

Me![diffRoundUp] = intMinDiff + (15  (intMinDiff Mod 15))

End If

Else

Me![diffRoundUp] = Null

End If

End Sub
Thats fantastic. Thanks a lot for your help
  Expert 2.5K+
P: 2,653

Thanks, but I want to always round up. Do you know of a variation of it to go up?
If you take a closer look to the expression you'll see that it rounds up (rounding down negative number means rounding up its absolute value).
Int(16/15)*15=30 for example
  Expert 5K+
P: 8,652

Thats fantastic. Thanks a lot for your help
Anytime, glad it worked out for you.
  Expert 5K+
P: 8,652

If you take a closer look to the expression you'll see that it rounds up (rounding down negative number means rounding up its absolute value).
Int(16/15)*15=30 for example
Interesting approach FishVal, it's almost like you have to think negatively (LOL).
  Expert 2.5K+
P: 2,653

Twas brillig, and the slithy toves
Did gyre and gimble in the wabe:
All mimsy were the borogoves,
And the mome raths outgrabe.
:P
Nothing new.
    Question stats  viewed: 3082
 replies: 13
 date asked: Sep 19 '07
