437,671 Members | 1,624 Online 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
13 Replies

 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

 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 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 Expand|Select|Wrap|Line Numbers "A time difference of 63 minutes Rounds Up to: " & fRoundUpTo15(63) & " minutes" OUTPUT: Expand|Select|Wrap|Line Numbers A time difference of 63 minutes Rounds Up to: 75 minutes Sep 21 '07 #4

 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 Dim diff As Integer, diffRoundUp As Integer   If diff Mod 15 = 0 Then   diffRoundUp = diff Else   diffRoundUp = diff + (15 - (diff Mod 15)) End If Sep 21 '07 #5

 P: 7 Actually, I like my 2nd idea better: Expand|Select|Wrap|Line Numbers 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 Oct 2 '07 #6

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

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

 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

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

 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

 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 