Time Calculations

[Guest]

I have two time fields in a query in h:nn:ss format. One is an average time
for a function for the last 60 days the other is an average for the current
day. I subtract the current day from the 60 day average to compare the
current average to the average. When I perform
60daytimeaverage-currentdayaverage the result is always a positive. How do I
get the negative time to show negative. An example of the results are:
0:25:40-0:17:26=0:07:44
0:30:17-0:26:15=0:04:02 (the result should be -0:04:02)

Thanks,
Scott

 

[Guest]

25 minus 17 will always result in a positive number.
30 minus 6 will always result in a positive number.

 

[Michel Walsh]

In Jet, a datetime value between -1 and +1 is formatted, by default, with
its absolute value:


? CDate(0.25), CDate(-0.25)
06:00:00 06:00:00


? Format( CDate(-.25), "Long Date"), " / ", Format( CDate(.25), "Long Date")
Saturday, December 30, 1899 / Saturday, December 30, 1899



You can try:


? iif(CDate(x) < 0, "-", "") & Format( CDate(x), "hh:nn:ss")


where x is the value to be printed.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

You're right, obviously. I transposed the data. The values should be:
25 minus 17 (a positive number) and
26 minus 30 (a negative number)

Thanks for your help.
Scott

 

[John Spencer]

But TIME is always a positive value since TIME is not duration it is a point
in time. There is no such time as negative 00:04:17.

You can try getting the results in seconds using the DateDiff function.

DateDiff("s",TimeField1,TimeField2)

Now you are talking number of seconds - which can be treated as duration.
You can do math on this a return a value that looks like a time

x \ 60 (returns the minutes) with a negative sign if x is negative
Abs(x Mod 60) returns the seconds without the minutes and removing the
negative sign if it is there.

Putting that all together (replace x with the DateDiff formula
x\60 & ":" & Format(Abs(x mod 60),"00")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

I have two time fields in a query in h:nn:ss format. One is an average time
for a function for the last 60 days the other is an average for the current
day. I subtract the current day from the 60 day average to compare the
current average to the average. When I perform
60daytimeaverage-currentdayaverage the result is always a positive. How do I
get the negative time to show negative. An example of the results are:
0:25:40-0:17:26=0:07:44
0:30:17-0:26:15=0:04:02 (the result should be -0:04:02)

I think this is an example of why Date/Time values should NOT be used to store
durations! If you're going to do sums or averages which might exceed 24 hours,
you'll find that a Date/Time value will not display the hours correctly: 25
hours is #12/31/1899 01:00:00# and will display as 01:00:00.

I would suggest using a Long Integer count of seconds to store your duration;
you can use an expression such as

[Duration] \ 3600 & Format([Duration] \ 60 MOD 60, ":00") & Format([Duration]
MOD 60, ":00")

to display it in hh:nn:ss appearance.

John W. Vinson [MVP]

 

[Guest]

Thank you. That got me half way there. The data now displays in "-hh:nn:ss"
format; however, I need to make one more calculation off the data but it
appears the data is now in text format and I'm not able to make a further
calculation off of it. Any ideas?

Thanks,
Scott

 

[Michel Walsh]

If you need the value in some other computation, you can use it "as it is",
without formatting the value (ie, use the initial value, NOT its format, a
STRING).


Indeed, consider:

? 2 + CDate(-0.25), CDate(2-0.25)
1899.12.31 18:00:00 1899.12.31 18:00:00



You see, even if

? CDate(-0.25)
06:00:00


'appears' to be positive, internally, it is still negative, as proven by the
result of 2 + CDate(-0.25)

being the same as CDate( 2-0.25). If CDate(-0.25) was removing the negative
sign, 2+CDate(-0.25) would have supplied the same as CDate(2.25).



Ok, trying to make things a little bit LESS confusing:

Internally, a date-time value is stored as come kind of floating point
value, where the integer part is the number of days since 30 Dec 1899 ( so
1.75 is one full day and three quarters of another one, past 30 Dec 1899
at 00:00:00 ). That goes fine EXCEPT for values between -1 and +1: they
will be formatted WITHOUT the day, and AS IF the value was positive.... but
that is only a matter of formatting. The real value is still stored with its
sign.

As John mentioned, past 24 hours, you will get a DATE in addition to the
hour-minute-second. So, if that may occur, use a non default FORMAT (as
illustrated by John).



Hoping it may help,
Vanderghast, Access MVP