By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,671 Members | 1,624 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,671 IT Pros & Developers. It's quick & easy.

Rounding

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
Sep 19 '07 #1
Share this Question
Share on Google+
13 Replies


FishVal
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
Sep 19 '07 #2

P: 7
Hi, thanks for that but it still returns the same result.

Any other ideas?
Sep 21 '07 #3

ADezii
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function fRoundUpTo15(MinDiff As Integer) As Integer
  2. Select Case MinDiff
  3.   Case 1 To 15
  4.     fRoundUpTo15 = 15
  5.   Case 16 To 30
  6.     fRoundUpTo15 = 30
  7.   Case 31 To 45
  8.     fRoundUpTo15 = 45
  9.   Case 46 To 60
  10.     fRoundUpTo15 = 60
  11.   Case 61 To 75
  12.     fRoundUpTo15 = 75
  13.   Case 76 To 90
  14.     fRoundUpTo15 = 90
  15.   Case 91 To 105
  16.     fRoundUpTo15 = 105
  17.   Case 106 To 120
  18.     fRoundUpTo15 = 120
  19. End Select
  20. End Function
Expand|Select|Wrap|Line Numbers
  1. "A time difference of 63 minutes Rounds Up to: " & fRoundUpTo15(63) & " minutes"
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. A time difference of 63 minutes Rounds Up to: 75 minutes
Sep 21 '07 #4

ADezii
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:
Expand|Select|Wrap|Line Numbers
  1. Dim diff As Integer, diffRoundUp As Integer
  2.  
  3. If diff Mod 15 = 0 Then
  4.   diffRoundUp = diff
  5. Else
  6.   diffRoundUp = diff + (15 - (diff Mod 15))
  7. End If
Sep 21 '07 #5

P: 7
Actually, I like my 2nd idea better:
Expand|Select|Wrap|Line Numbers
  1. Dim diff As Integer, diffRoundUp As Integer
  2.  
  3. If diff Mod 15 = 0 Then
  4.   diffRoundUp = diff
  5. Else
  6.   diffRoundUp = diff + (15 - (diff Mod 15))
  7. 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
Oct 2 '07 #6

ADezii
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:[
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. If Not Me.NewRecord And Not IsNull(Me![Start]) And Not IsNull(Me![End]) Then                                          '*
  3.   Dim intMinDiff As Integer
  4.  
  5.   intMinDiff = DateDiff("n", Me![Start], Me![End])
  6.  
  7.   If intMinDiff Mod 15 = 0 Then
  8.     Me![diffRoundUp] = intMinDiff
  9.   Else
  10.     Me![diffRoundUp] = intMinDiff + (15 - (intMinDiff Mod 15))
  11.   End If
  12. Else
  13.   Me![diffRoundUp] = Null
  14. End If
  15. End Sub
Oct 2 '07 #7

FishVal
Expert 2.5K+
P: 2,653
Hi, tmdan.

You may advantageously use Int() function that always rounds down.
rounddif: -Int(-[diff]/15)*15
Oct 2 '07 #8

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?
Oct 2 '07 #9

P: 7
Placing this code in your Form's Current() Event should do the trick:[
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. If Not Me.NewRecord And Not IsNull(Me![Start]) And Not IsNull(Me![End]) Then                                          '*
  3.   Dim intMinDiff As Integer
  4.  
  5.   intMinDiff = DateDiff("n", Me![Start], Me![End])
  6.  
  7.   If intMinDiff Mod 15 = 0 Then
  8.     Me![diffRoundUp] = intMinDiff
  9.   Else
  10.     Me![diffRoundUp] = intMinDiff + (15 - (intMinDiff Mod 15))
  11.   End If
  12. Else
  13.   Me![diffRoundUp] = Null
  14. End If
  15. End Sub
Thats fantastic. Thanks a lot for your help
Oct 2 '07 #10

FishVal
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
Oct 2 '07 #11

ADezii
Expert 5K+
P: 8,652
Thats fantastic. Thanks a lot for your help
Anytime, glad it worked out for you.
Oct 2 '07 #12

ADezii
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).
Oct 2 '07 #13

FishVal
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.
Oct 2 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.