Using a query to sum timings together (hours/mins) 2

[aarondewberry]

Hi

I have three fields: actual hours, scheduled hours and Net Flexi. In all of the fields will be timings (hours,minutes)
ie.
actual hours - 07:12 (7 hours, 12 minutes)
scheduled hours - 03:36 (3 hours, 36 minutes)
net flexi - 02:00 (2 hours)

What i need to do is to be able to sum all three field timings together.

ie. 07:12 + 03:36 + 02:00 = 12:48 (12 hours, 48 mins)

Also in some instances i might have to minus one timing away from another.

Can anyone help? many thanks

 

[PHV]

A starting point:
TimeValue([actual hours])+TimeValue([scheduled hours])+TimeValue([net flexi])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Just an added note. PHV's suggestion will work fine as long as the total is less than 24 hours. If it is more than 24 hours then you will need something more elaborate

z = TimeValue([actual hours]) +
    TimeValue([scheduled hours]) +
    TimeValue([net flexi]) 

Int(z * 24) & ":" & (z * 1440) Mod 60

 

[aarondewberry]

Thanks guys that works a treat.

But I am having trouble with subtracting.
I have tried using the same method as PHV suggested, but just replaced the '+' with a '-'
ie. TimeValue([actual hours])-TimeValue([scheduled hours])
but that doesn't work.

Any ideas??

 

[PHV]

but that doesn't work
What a meaningful assertion !
what happens ? Computer crash ? Any error message ? Unexpexted result ? ... ???

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[aarondewberry]

Ha Ha. Sorry I'll be more specific.

The actual hours = 14:24
The Scheduled hours = 04:00

When you add them it comes up with 18:24, which is fine.
But if you change the '+' with a '-' instead of coming up with 10:24 as expected, it comes up with 0.433333.

 

[PHV]

And what about this ?
CDate(TimeValue([actual hours])-TimeValue([scheduled hours]))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[aarondewberry]

Excellent, works a treat. Thanks very much!!