adding multiple time cards in time format that will be over 24:00 1

[lisagippe]

I have a query that pulls the login and logout times for my agents from a table and calculates the difference. This works great, however, when I try to add all these times together for a weekending total it won't give me the format I am looking for, which would be 40:39 (hrs:mins). The agents work days would be 8:07, 8:12, 8:06 etc. How can I format the results to show me this longer time format? Thanks for the help!

 

[lisagippe]

Save is just a general# calculated field, it contains some minor adding and subtracting of other fields. None of which are date/time formatted.

24*Int([Save])+Hour([Save]) & Format(Sum([Save]),':nn')
This formula converts it to a time format.

The formula I am having trouble with should be summing that formula for the dates I have slected:
Sum(24*Int([Save])+Hour([Save])) & Format(Sum([Save]),':nn')

This is where the Int in the formula is rounding too much.

 

[dhookom]

I can't answer without some sample values of [Save] and how they calculate and what you would expect for a returned value.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lisagippe]

No problem, let's see if this helps.

Save: ([TotalTime]-[Lunch Time])+[Sick1]+[Vac1]+[Flt1]+[Other1]
[Save] gives me an agents total hours for a given day including sick, vac etc. It is returned as a decimal value, for an employee who works an 8:02 day it returns 0.33472222222.

This formula: Total: 24*Int([Save])+Hour([Save]) & Format([Save],':nn') - takes the [Save] decimal and converts into the time format which works fine.

What I need returned is a sum of a weeks worth of hours in time format ie 40:19.

 

[PHV]

And what about this ?
24*Int(Sum([Save]))+Hour(Sum([Save])) & Format(Sum([Save]),':nn')

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

 

[lisagippe]

Sorry for the delay, but thank you it works beautifully!! You guys have been such a huge help I can now finalize everything and show Management, thank you thank you thank you!!!