Calculating diff between times if > 24hrs

[robyb]

Hi All,

I Have 2 fields, TimeOn and Timeoff. Both are time fields, and I have a control on a report showing the difference between them (simple enough, eh?). The problem comes when I try to total all of these at the bottom of the report. As they are often totalling more than 24 hours, the sum is tripping over itself and only showing the amount over 24, ie if the list comes to 29 hours, the textbox only shows 5 hours, ie it ignores anything that is a multiple of 24 hours.

Does anyone know a different formula I should be using? The existing one is just as very simple =sum([TimeOff]-[TimeOn])

Many Thanks

 

[Ukraine82]

Are you trying to subtract anything that is over 24 hours.

For example if you have 31 hours - 24 hours. You only want the field to show 6 hours?

If so, try this DateDiff("h",[TimeOff],[TimeOn])-24

Be sure to set you field for TimeOff and TimeOn to General Date.

hth,

Michael

 

[Ukraine82]

Ignore my last post

Try this DateDiff("h",[TimeOff],[TimeOn])

hth,

Michael

 

[robyb]

I've tried your second suggestion, and and now getting the result of 00:00

The result I'm looking for is if it is 29 hours, then it shows 29 hours.

I guess I should have used field containing the date and time when I designed the database!

Thanks for trying

Rob

 

[Ukraine82]

Set your field to general date.

Take a look at the attachment

Look at the query I made and you can run through a report.

hth,

Michael

 

[robyb]

I think I can see the problem now I've looked at your database.

In the table on mine, I didn't have a format in the date/time field, only an input mask. I've now changed this, and will try some fresh data to see how it runs on Monday morning.

Thanks for the help

Rob

 

[Ukraine82]

Glad I can help and good luck.

Michael