Time Calculations

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
25 minus 17 will always result in a positive number.
30 minus 6 will always result in a positive number.
 
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
 
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
 
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
..
 
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)
Click to expand...

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]
 
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
 
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
 
Back
Top