Time over 24 hours
Drum2001
have report that calculates the number of regular hours worked per
week.
Currently I am running the following query:
----------------------------------------------------------------------
SELECT DateFilter.Employee, DateFilter.TimeTypeIn,
Sum([ClockOut]-[ClockIn]) AS Expr1
FROM DateFilter
GROUP BY DateFilter.Employee, DateFilter.TimeTypeIn
HAVING (((DateFilter.TimeTypeIn)="Regular"));
ClockIn & ClockOut are both Time/Date
----------------------------------------------------------------------
Everything calculates correctly up to 24 hours. After that, it messes
up. I would like the sum to say something like 38:25:02
Any suggestions would be appreciated!
Wayne Morgan
you want hours and minutes, do the calculation in minutes. Format it later
the way you want it after you get the total. Instead of subtracting ClockIn
from ClockOut, use the DateDiff function and tell it to return minutes (n).
In your example, it appears you're wanting to track to the second, is that
correct?
Example:
TotalTime:Sum(DateDiff("s", [ClockIn], [ClockOut])) \ 3600 &
Format((Sum(DateDiff("s", [ClockIn], [ClockOut])) Mod 3600) / 86400,
":nn:ss")
Example from the Immediate window:
?10000 \ 3600 & Format((10000 Mod 3600) / 86400, ":nn:ss")
2:46:40
The problem you're running into is that you want elapsed time. An elapsed
time isn't a "time" data type, it is simply a number. For example, the
difference between 2pm and 3pm is 1 hour, not 1 o'clock (1:00). You are then
wanting it formatted as if it was a time.
--
Wayne Morgan
MS Access MVP
"Drum2001" <drum...@gmail.com> wrote in message
news:1140794371.4...@v46g2000cwv.googlegroups.com...
Drum2001
Now here is a little twist... How would I make it so anything over 40
hours would be overtime?
Basically how would I code: TotalTime - 40:00:00 = (overtime)
Bob Quintal
news:1140806460....@u72g2000cwu.googlegroups.com:
since the total time in seconds is sum(datediff("ss",[clockin],
[clockout]) as given by wayne,
Overtime in seconds is
IIF(sum(datediff("s",[clockin],[clockout])-40*60*60) >0, sum
(datediff("s",[clockin],[clockout])-40*60*60,0))
and that has to be reformatted as Wayne showed, .
Overtime =
IIF(sum(datediff("s",[clockin],[clockout])-40*60*60 >0, sum
(datediff("s",[clockin],[clockout])-40*60*60,0)/3600 &
Format((IIF(sum(datediff("s",[clockin],[clockout])-40*60*60 >0,
sum(datediff("s",[clockin],[clockout])-40*60*60,0) Mod 3600) /
86400, ":nn:ss")
which has to go into the query as a single line, is quite a
mess, and I'm sure I'm missing some () so use the User defined
function below.
overtime = SecToDur(IIF(sum(datediff("s",[clockin],[clockout])-
40*60*60 >0, sum(datediff("s",[clockin],[clockout])-40*60*60,0))
Public Function sec2dur(seconds As Long) As String
On Error Resume Next
Dim hrs As Long
Dim mins As Integer
Dim secs As Integer
hrs = Int(seconds / 3600)
mins = Int((seconds - (3600 * hrs)) / 60)
secs = seconds - (hrs * 3600 + mins * 60)
sec2dur = Format(hrs, "#,##0") _
& ":" & Format(mins, "00") & ":" _
& Format(secs, "00")
End Function
--
Bob Quintal
PA is y I've altered my email address.
Drum2001
overtime = SecToDur(IIF(sum(datediff("s",[clockin],[clockout])-
40*60*60 >0, sum(datediff("s",[clockin],[clockout])-40*60*60,0))
In the query, it says "The Expression you entered has a function
containing the wrong number of arguments."
Bob Quintal
news:1141047864.4...@i39g2000cwa.googlegroups.com:
I parsed out your code, you are missing a parenthesis somewhere.
overtime =
SecToDur(
IIF(
sum(
datediff(
"s",[clockin],[clockout]
)
- 40*60*60 >0,
sum(
datediff(
"s",[clockin],[clockout]
)-40*60*60,0
)
)
1) in a query, you need a colon to define the column, instead of
an equals,
2) the nesting of parentheses can be a royal PITA. I oftem build
in notepad, tabbing in for each ( and out per ), to make sure
they're in the right places.
Overtime:
sectodur(
IIF(
sum(
datediff(
"s",[clockin],[clockout]
)
)-40*60*60 >0
,
sum(
datediff(
"s",[clockin],[clockout]
)
)-40*60-60
,
0
)
)
With this level of nested calls, I'd probably move the code to a
function.
Drum2001
It looks like were closer. It seems as if it is subtracting 41 mins,
not 40 hours. Any suggestions?
With sample times entered, the queries come up with the following
results...
T
he Total Regular time = 51:59:31
The Total Overtime = 51:18:31
Bob Quintal
news:1141137870.6...@p10g2000cwp.googlegroups.com:
found this in the code I posted: )-40*60-60
which would give the exact 41 minutes instead of 40 hours error
you mentioned. It follows the second datediff. )-40*60*60 will
give the correct result. So sorry I can't type.
My employer keeps me on staff for my mental acuity, not my
physical dexterity.
Drum2001
Thank you very much for all your assistance!
That did work!
Drum2001
then have Regular Time display = 80:00:00
If under 80:00:00, then display the correct hours
Bob Quintal
news:1142871909....@t31g2000cwb.googlegroups.com:
> Is there anyway to make it like the following. If there is
> overtime, then have Regular Time display = 80:00:00
>
> If under 80:00:00, then display the correct hours
>
I could not get the original messages back via my ISP Google.
Anyway, It's just a matter of building up the calculations and
nesting them in IIF statements.
so if you have TotalTime (in seconds)
RegularTime is
iif(TotalTime > 80*60*60,80*60*60,Totaltime).
OverTime is
iif(TotalTime > 80*60*60,totaltime-80*60*60,0).
Those would go inside the SecToDur() function that I originally
provided in 2004
Public Function sec2dur(seconds As Long) As String
On Error Resume Next
Dim hrs As Long
Dim mins As Integer
Dim secs As Integer
hrs = Int(seconds / 3600)
mins = Int((seconds - (3600 * hrs)) / 60)
secs = seconds - (hrs * 3600 + mins * 60)
sec2dur = Format(hrs, "#,##0") & ":" & Format(mins, "00") & ":"
&
Format(secs, "00")
End Function