Rounding

[tmdan]

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

[FishVal]

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

[tmdan]

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

Any other ideas?

[ADezii]

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:
[CODE=vb]Public Function fRoundUpTo15(Mi nDiff 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[/CODE]
[CODE=vb]"A time difference of 63 minutes Rounds Up to: " & fRoundUpTo15(63 ) & " minutes"[/CODE]
OUTPUT:
[CODE=text]A time difference of 63 minutes Rounds Up to: 75 minutes[/CODE]

[ADezii]

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

Any other ideas?

Actually, I like my 2nd idea better:[CODE=vb]Dim diff As Integer, diffRoundUp As Integer

If diff Mod 15 = 0 Then
diffRoundUp = diff
Else
diffRoundUp = diff + (15 - (diff Mod 15))
End If[/CODE]

[tmdan]

Actually, I like my 2nd idea better:[CODE=vb]Dim diff As Integer, diffRoundUp As Integer

If diff Mod 15 = 0 Then
diffRoundUp = diff
Else
diffRoundUp = diff + (15 - (diff Mod 15))
End If[/CODE]

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

[ADezii]

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:[[CODE=vb]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[/CODE]

[FishVal]

Hi, tmdan.

You may advantageously use Int() function that always rounds down.
rounddif: -Int(-[diff]/15)*15

[tmdan]

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?

[tmdan]

Placing this code in your Form's Current() Event should do the trick:[[CODE=vb]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[/CODE]

Thats fantastic. Thanks a lot for your help

[FishVal]

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

[ADezii]

Thats fantastic. Thanks a lot for your help

Anytime, glad it worked out for you.

[ADezii]

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).

[FishVal]

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.