Not Rounding

[noxum]

I have a formula that gives me the minutes between 2 times.
When I print this time it gets rounded up in the Format function.

12:20:54 - 12:21:06 = .017

I want to display the total minutes as 1 minute, but when I put it into the Format forumula it rounds it to 2 minutes. Does anyone have any ideas.

noxum

 

[robctech]

I posted code earlier today for doing that precise thing. goto this thread thread181-27567

 

[noxum]

It doesn't look as though this thread says anything about the rounding issue? Confussion is setting in....

 

[ADoozer]

could you post your formula/code?

im struggling to see how using format function on 0.017 gives you a value of 2!!!

If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
A General Guide To Excel in VB FAQ222-3383
The Great Date Debate Thread222-368305
File Formats Galore @

http://www.wotsit.org

or

http://www.filext.com

 

[johnwm]

robctech
Could you check your thread referenced above please? It may be missing a digit.

noxum
Can you show how you got:
12:20:54 - 12:21:06 = .017
It doesn't appear to be seconds, minutes or hours


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[CCLINT]

Time is Date.

And Date is a numeric figure which returns days and portion of days.

So
?(CDate("20:00:00&quot-CDate("23:00:00&quot)

Will return the portion of a day.

Which is the same as:
?CSng(CDate("20:00:00&quot)-CSng(CDate("23:00:00&quot)

There are 1440 minute in a day. So

?(CDate("20:00:00&quot-CDate("23:00:00&quot)*1440

will return 180 minutes, or 3 hours.

Between 12:21:06 and 12:20:54 only 12 seconds have passed.
You need to decide if this should round, round up only, round down only, or not round at all.

You round using the ROUND function or the CLNG or CINT functions.
You round down, just chopping off the decimal portion, using the FIX or INT functions.
You round up by adding 1 and using the FIX or INT function:

Round 1.95555 or 1.25555 up to the next whole number:

Int(1.95555 + 1) = 2
Int(1.25555 +1) = 2


Now, your problem is your formula is using the ABS and INT function on a negative number.

1 - 2.5 = -1.5
Using the INT function you will get -2 and the FIX function you will get -1:

?Abs(Int(1-2.5)) = 2

?Int((2.5-1)) = 1

?Abs(Fix(1-2.5)) = 1

Or, swap the numbers/Dates to be calculated around so that the larger numer/newer date is first....

See if this helps some (think about the possibilities, check out VB HELP on these functions, etc)

 

[MichaelRed]

I also canno get the math to work (e.g. get the 0.17 somethings). The format "function" works quite nicely, however, on the basic calculation:

? fORMAT(#12:20:54# - #12:21:06#, "hh:mm:ss&quot
00:00:12


Note that to use LITERALS it is necessary to enclose the time values in the Date / time markers. In the more general sense, one would be poassing date / time VARIABLES to the expression and these would not require the 'markers'.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[CCLINT]

Well, I was trying to get them to see this:

?Abs(Fix((#12:19:54#-#12:21:06#)*1440))

>I have a formula that gives me the minutes between 2 times.

If you don't want a value returned just in minutes, but hours and seconds as well then the format function will work.

?Format((#11:19:54# - #12:21:06#),"hh:mm:ss&quot = 01:00:12

I depends what you are looking for.
noxum asked for the minutes between two values.
Here, the format function is out of place.

However, if one is not interested in just minutes, then of course it is useful here.

 

[MichaelRed]

Well,

I seem to always walk on the 'wrong' side of the "line" between teaching, insulting and obfuscating - perhaps because the concept of a line between three points (or even area) is a poor or difficult concept. Here, I was just thinking of the Shakesperian concept ("Much Ado About Nothing" and was HOPING to end a trivial bit of 'chatter' amongst dilletants. Alas, alack and obviously far, far awry (AGAIN). Sorry to rain on the parade.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[noxum]

Sorry about the delayed response to your posts!!!!! I don't have access to the forum @ the home front.
I don't have the code infront of me now, so some of it is coming from memory, namely the 86400 number, might be 84600:

To get the result of .017, I am using the DateDiff Function.

starttime = 12:20:54
endtime = 12:21:06

timeDiff = format(DateDiff("s", starttime, Endtime)/86400,"0.000"

timeDiff now equals .017

When I save to the DB I would like it to be saved as .01 not .02. when I use the format option it rounds. Is there a way or a better way?

 

[Mosaic1]

Maybe try something like this.

D = Int(0.017 * 100)
D = 0.01 * D