Another time calculation issue, thanks for any help!

[Gina Whipp]

Hello All,

I was hoping to do this without to much difficulty but not having much luck.
I got the round down to the nearest 15 minutes. NOW I would like to round
up to the nearest 15 minutes (ie. .25, .5, .75 ONLY), in fifteen minute
intervals. Here's some of what I've tried so far...

Round(1.2/0.6,0) answer: 2 would like 1.25
Int(1.9*8.0+0.6)/8.0 answer: 1.875 would like 2
-Int(-1.2)/60 answer 1.66 would like 1.25

I store the time in decimal format (I need that for another calculation
which works perfectly).

Any Ideas?
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

 

[Robert Morley]

Round(1.2/0.6,0) answer: 2 would like 1.25

I haven't looked at the others, but I think you're going to have to explain
a bit more about how you're doing things, as this SHOULD be 2, using any
math:

1.2/0.6 = 2, so
Round(2,0) = 2



Rob

 

[Gina Whipp]

Robert,

I am trying to round TimeDecimal to the NEXT 15 minute interval. I am not
saying the answer is wrong but it's not where I want to go. For billing
purposes I want to go the the next quarter hour.

1.2 = 1.25
1.3 = 1.5
1.6 = 1.75
1.8 = 2

 

[Robert Morley]

Okay, so how are you looking at your Round example, then?

Should Round(1.2/0.6,0) actually become Round(1.25/0.75,0)?



Rob

 

[John W. Vinson]

Okay, so how are you looking at your Round example, then?

Should Round(1.2/0.6,0) actually become Round(1.25/0.75,0)?

Gina, I take it you're trying to round UP rather than round DOWN?

If so, try

Round([expression] + 0.2499999, 0)


John W. Vinson [MVP]

 

[Gina Whipp]

I am trying to get to my ultimate goal. In the Round formula the 1.2 is MY
time decimal field, it will not say 1.25 to be /0.75. I am trying to round
up and I was only using that as one of the solutions I tried that didn't
work.

 

[Gina Whipp]

Yes, I am.

Your formula gives me 1 and not 1.25. I am now playing with this one and it
seems to do what I want: Int(1 + [TimeDecimal] * 4) / 4 EXCEPT if the time
decimal is 1.25 or 1.5 or 1.75 it want to round up and I would like it to
except those values. I feel and if then else statement coming on....

1.2 = 1.25
1.6 = 1.75
1.8 = 2

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

Okay, so how are you looking at your Round example, then?

Should Round(1.2/0.6,0) actually become Round(1.25/0.75,0)?

Gina, I take it you're trying to round UP rather than round DOWN?

If so, try

Round([expression] + 0.2499999, 0)


John W. Vinson [MVP]

 

[Robert Morley]

Okay, so going back to the previous formula that I gave you, just integrate
it with John's suggestion, and I believe you should get what you're looking
for:

Int(([aTimeDecimal] + 0.24999999999) * 4) / 4

Or you can make it look a little cleaner by doing the * 4 yourself and then
you get:

Int([aTimeDecimal] * 4 + 0.99999999999) / 4



Rob

 

[Guest]

Is this what you are looking for?

Public Function RoundUp(Num As Double, Mins As Byte) As Double
Dim Remainder As Integer

Remainder = Num * 60 Mod Mins

If Remainder > 0 Then
RoundUp = ((Num * 60) + (Mins - Remainder)) / 60
Else
RoundUp = Num
End If
End Function

RoundUp(1.2, 15) results in 1.25 and so on...

Steve

 

[Gina Whipp]

That looks like it works perfectly, playing with it now... Robert you are a
hair saver! Big THANK YOU!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

Okay, so going back to the previous formula that I gave you, just
integrate it with John's suggestion, and I believe you should get what
you're looking for:

Int(([aTimeDecimal] + 0.24999999999) * 4) / 4

Or you can make it look a little cleaner by doing the * 4 yourself and
then you get:

Int([aTimeDecimal] * 4 + 0.99999999999) / 4



Rob

I am trying to get to my ultimate goal. In the Round formula the 1.2 is
MY time decimal field, it will not say 1.25 to be /0.75. I am trying to
round up and I was only using that as one of the solutions I tried that
didn't work.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II

 

[Gina Whipp]

SteveM,

Okay, now you're just showing off...

Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

 

[Guest]

Robert's solution is slick!
The function I gave you will alow you to use different intervals of
time...if you should need to.

Steve

 

[Guest]

I realize you have a solution, but I thought I would throw this into the mix
as I find it easier on the eyes, plus it makes sense to me....so ... to round
UP to the nearest interval:

-Int(-1.2/.25) * .25 = 1.25
-Int(-1.3/.25) * .25 = 1.5

Or generically ...

-Int(-<expression> / <interval>) * <interval>

This works because the of the behavior of Int() with negative numbers. The
Int() fuction will truncate a value to the first whole number that is less
than the argument ...

Int(-4.5) = -5
Int(-3.1) = -4

So if I want the "Ceiling" of 3.1 I can take the negative of it ..
-3.1
send it to the Int() function ...
Int(-3.1) = -4

The take the negative of the result ..
-Int(-3.1) = 4

Then the interval part is just some math.

Hope that helps!

--
Regards,
Brent Spaulding
datAdrenaline | Access MVP

That looks like it works perfectly, playing with it now... Robert you are a
hair saver! Big THANK YOU!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

Okay, so going back to the previous formula that I gave you, just
integrate it with John's suggestion, and I believe you should get what
you're looking for:

Int(([aTimeDecimal] + 0.24999999999) * 4) / 4

Or you can make it look a little cleaner by doing the * 4 yourself and
then you get:

Int([aTimeDecimal] * 4 + 0.99999999999) / 4



Rob

I am trying to get to my ultimate goal. In the Round formula the 1.2 is
MY time decimal field, it will not say 1.25 to be /0.75. I am trying to
round up and I was only using that as one of the solutions I tried that
didn't work.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II
Okay, so how are you looking at your Round example, then?

Should Round(1.2/0.6,0) actually become Round(1.25/0.75,0)?



Rob

Robert,

I am trying to round TimeDecimal to the NEXT 15 minute interval. I am
not saying the answer is wrong but it's not where I want to go. For
billing purposes I want to go the the next quarter hour.

1.2 = 1.25
1.3 = 1.5
1.6 = 1.75
1.8 = 2

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II
Round(1.2/0.6,0) answer: 2 would like 1.25

I haven't looked at the others, but I think you're going to have to
explain a bit more about how you're doing things, as this SHOULD be 2,
using any math:

1.2/0.6 = 2, so
Round(2,0) = 2



Rob

 

[Robert Morley]

-Int(-<expression> / <interval>) * <interval>

I don't believe I'd seen that method before, but it makes perfect sense now
that I have. Thanks for the tip!


Rob

 

[Guest]

You are quite welcome! ... I wish I had thought of it on my own as it does
make sense, but someone (Van T Dinh) showed me that a while back ... and It
stuck.

 

[Gina Whipp]

Guys, I have so many GREAT solutions now I'm not sure what to do with
myself! Thanks for ALL your help!

 

[Arvin Meyer [MVP]]

Just saw this thread. Here's one I've been using for more than 10 years that
rounds any amount in any direction:

Option Compare Database
Option Explicit
Public Const vb_roundup = 1
Public Const vb_rounddown = 0

Function Rnd2Num(Amt As Variant, RoundAmt As Variant, _
Direction As Integer) As Double
On Error Resume Next
If Direction = vb_rounddown Then
Rnd2Num = Int(CDec(Amt) / RoundAmt) * RoundAmt
Else
Rnd2Num = -Int(CDec(-Amt) / RoundAmt) * RoundAmt
End If
End Function

?Rnd2Num(1.09,.25,1)
1.25

 

[Gina Whipp]

Glad I'm still watching it! Thanks Arvin!!!

 

[Robert Morley]

Note that if speed is a concern (i.e., if you're calling this thousands of
times in a loop), you should use one of the earlier formulas. The earlier
variations are functionally equivalent to this latest one, but the latest
one uses Decimal values, which are notoriously slow. If you have a need to
handle extraordinarily large numbers though (like tracking the number of
hours all people have worked everywhere since the dawn of time), Decimal is
the way to go.

This function also allows you to choose to round up or down, however, which
is a nice touch, and can easily be applied to the faster data types if
desired.


Rob

 

[Gina Whipp]

Robert,

I am using on of the earlier ones but it is a 'cool' approach and one that
may come in handy one day, as I am using decimal values (I am using because
of how the Client wants to enter the data). I also like the way I can use
it Up/Down as in the project I am doing now I actually need both ways and
rather than have two functions I would only need one. Come to think of it,
I MIGHT use it now. I think I just talked myself into it!

I also wanted to thank Arvin for taking the time to post the function he
uses. (Too few thanks in this forum and I didn't want to be one of those
folks!)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II