Rounding

2,653

Expert 2GB

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

tmdan

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

Any other ideas?

Sep 21 '07 #3

ADezii

8,834

Expert 8TB

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

ADezii

8,834

Expert 8TB

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

tmdan

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

ADezii

8,834

Expert 8TB

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

2,653

Expert 2GB

Hi, tmdan.

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

Oct 2 '07 #8

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?

Oct 2 '07 #9

tmdan

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

2,653

Expert 2GB

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

8,834

Expert 8TB

Thats fantastic. Thanks a lot for your help

Anytime, glad it worked out for you.

Oct 2 '07 #12

ADezii

8,834

Expert 8TB

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

vb.net 2003 rounding decimal value question

2,653

Expert 2GB

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

by: Norvin Laudon | last post by:

Hi, Can somebody explain the following, from the MSDN documentation for the "System.Convert.ToInt32(double)" function <quote> Return Value value rounded to the nearest 32-bit signed...

C# / C Sharp

by: spebola | last post by:

I am using vb.net 2003 professional and I get the following results when using the round method: dim Amount as decimal = 180.255 Amount = Amount.Round(Amount, 2) Amount now contains 180.25. ...

Rounding, are we MAD????

by: Zorpiedoman | last post by:

Howcome: Dim D as decimal = .5D msgbox d.Round(D, 0) this returns "0" Now when I went to school .5 rounds UP to 1 not DOWN to zero?????!!! Documentation says this, but what the heck are...

acceptable rounding practices in banking.

Rounding in PGSQL

by: Jiri Nemec | last post by:

Hello all, I have got one table with rounding values, table contains prices and round types. id price_from price_to rounding 1 0 1500 0.1 2 1500 ...

PostgreSQL Database

by: cj | last post by:

Lets assume all calculations are done with decimal data types so things are as precise as possible. When it comes to the final rounding to cut a check to pay dividends for example in VB rounding...

rounding to 9's

by: jdrott1 | last post by:

i'm trying to round my currency string to end in 9. it's for a pricing application. this is the function i'm using to get the item in currency: FormatCurrency(BoxCost, , , , TriState.True) if...

lrint and rounding mode

by: Spoon | last post by:

Hello everyone, I don't understand how the lrint() function works. long lrint(double x); The function returns the nearest long integer to x, consistent with the current rounding mode. It...

206

Rounding double

by: md | last post by:

Hi Does any body know, how to round a double value with a specific number of digits after the decimal points? A function like this: RoundMyDouble (double &value, short numberOfPrecisions) ...

Rounding a floating point number

by: jacob navia | last post by:

Hi "How can I round a number to x decimal places" ? This question keeps appearing. I would propose the following solution #include <float.h> #include <math.h>

Rounding a number to nearest even

by: bdsatish | last post by:

The built-in function round( ) will always "round up", that is 1.5 is rounded to 2.0 and 2.5 is rounded to 3.0. If I want to round to the nearest even, that is my_round(1.5) = 2 # As...

Python

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...