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!

 

[dhookom]

Is there a good reason to not show this a either hours with a decimal
=Sum(DateDiff("n",TimeStart,TimeEnd))/60
or minutes
=Sum(DateDiff("n",TimeStart,TimeEnd))

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]

Well I have to simplify it for the Supervisors. They can't figure out that 40.17 means 40 hrs 28 minutes.

If I convert everything to minutes would the formula below (given by a colleague) help me to format it into hr:mn

=[txtAvgStaff]\3600 & Format([txtAvgStaff] Mod 3600,"\:00\:00")

 

[SkipVought]

Hi,

You ought to have DATE & TIME in your calculation.

Skip,
[sub]
[red][/red]
[/sub]

 

[PHV]

What about a formula like this ?
24*Int(TotalTime)+Hour(TotalTime) & Format(TotalTime,':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]

Okay the formula PHV provided is sort of working maybe you can tell me what I am doing wrong...

The first query actually calculates TotalTime (LogOut-LogIn), the second one needs to Sum all the Total Times for the selected days. But, it keeps telling me it's a data type mismatch. Any ideas? I may be making this more difficult than it is, any simpler ideas with what I have provided are totally welcome! Thanks!

 

[dhookom]

The solution assumes LogOut and LogIn are both date/time data types and they are not null. To sum the durations, you would need to replace the TotalTime in PH's reply with (LogOut-LogIn).

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]

 

[PHV]

Any ideas?
Without the SQL code(s), not much ...
it's a data type mismatch
in which expression ?

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

 

[lisagippe]

I did replace the TotalTime with the LogOut-LogIn and for a daily summary it works fine, it's just in the 2nd query where I need a weekly total that it does the "data type mismatch in criteria expression". The 2nd query just pulls the agents name then sums the Total field (where the error is produced)

 

[PHV]

sums the Total field
Any chance you could post the relevant expression ?

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

 

[lisagippe]

This is the SQL for the first one (Daily) which works fine:

SELECT tbl_CSV_payroll.Name, 24*Int(([LogOut]-[LogIn]))+Hour(([LogOut]-[LogIn])) & Format(([LogOut]-[LogIn]),':nn') AS Total
FROM tbl_CSV_payroll;


This is the second one (Weekly):

SELECT Query1.Name, Sum(Query1.Total) AS SumOfTotal
FROM Query1
GROUP BY Query1.Name;

 

[dhookom]

You are attempting to Sum() a concatenated text field. Try remove the formatting stuff in your first query so you are still returning a date/time value.

SELECT tbl_CSV_payroll.Name, ([LogOut]-[LogIn]) AS Total
FROM tbl_CSV_payroll;

Then sum the Total field which is still summable.
SELECT Query1.Name, Sum(Query1.Total) AS SumOfTotal
FROM Query1
GROUP BY Query1.Name;

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]

 

[PHV]

Query1:
SELECT Name, 24*Int(LogOut-LogIn)+Hour(LogOut-LogIn) & Format(LogOut-LogIn,':nn') AS Total, LogOut-LogIn AS TotalTime
FROM tbl_CSV_payroll;

Weekly query:
SELECT Name, Sum(24*Int(TotalTime)+Hour(TotalTime)) & Format(Sum(TotalTime),':nn') AS SumOfTotal
FROM Query1
GROUP BY Name;

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

 

[lisagippe]

Oh my god it worked!!! Thank you guys so much for all your help you are life savers!!

 

[lisagippe]

Okay sorry one more issue.
Everything above works great, and I would like to use it in another column which would be TotalTime - LunchTime etc. It works great except when the agent didn't go to lunch, so if they have a TotalTime but no LunchTime it won't give me any result not even an error. It is only an issue when there is no Lunch all other results are fine. Any suggestions?

 

[dhookom]

I expect LunchTIme might be null. Is this two fields or one? Is it possible the value is null? Is there an expression that you could share with us that didnt' work? How about some sample records?

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]

Okay I resolved the issue above but most of the totals aren't correct. The Int contained in the formula is rounding down and causing some of the agent totals to be off as much as 4 hours. Other agents are correct. There is no error or abnormalities in the data.

 

[dhookom]

Could you please provide us with the expression you attempted to use?

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]

SumOfTotal: Sum(24*Int([Save])+Hour([Save])) & Format(Sum([Save]),':nn')

 

[dhookom]

WHat is [Save]? Is this a date/time field? What does it have to do with earlier questions?

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]