Calculate minus time averages

[lizcliff]

I have come stuck with different types of times fields.

i have one time minus another in an access query which i then export to excel pivot and within the pivots i have that time as an average - but when it is a minus time it wont display!
so then i thought i could change the calculation to a short time and export it like that - but it wont calculate it at all.

is there a function i can use - i dont need to display the time as a minur time, because i will be grouping it as over and under counts anyway?

thanks
Liz x

 

[John Big Booty]

Re: Calculate minue time averages

Have a look at the various Date/Time manipulation functions here.

 

[gemma-the-husky]

Re: Calculate minue time averages

you need to know what you are doing with times.

times ARE handling correctly, arithmetically.

but a time is ACTUALLY (ie represented as) a decimal portion of a day. so 6hrs is a quarter of a day, and is 0.25 day.

therefore all calculations involving manipulating time will end up as a discrete numer of days, and a "time remainder" - which is why your negative time is not working out correctly. you have negative days, but always positive time!

eg 4.25 days...... 4 days, 6hrs.

HOWEVER, when you display a time field, you only ever see the time bit of the total.

knowing this, you can actually manipulate the "true" time NUMERIC value to get the result you actually want.

eg 4.25 days multiplied by 24, gives a value of 102 (ie 102 hrs), which is the correct value.