looking for help with a time clock query

G

Guest

My query has an IN_TIME and an OUT_TIME with the date and time in same field
like shown below
how can i figure out the actual time that employee has worked
IN_TIME
12/1/2006 8:47:03 AM
OUT_TIME
12/1/2006 6:52:13 PM

Thanks for any help
 
G

Guest

This will do it --
Worked: DateDiff("h", [IN_TIME], [OUT_TIME]) & "Hours " & (DateDiff("n",
[IN_TIME], [OUT_TIME])) Mod 60 & "Minutes"
 
G

Guest

Ty Karl
That works
I should have gave alittle more info. Im needing something like if
they worked 09Hours 28minutes there total hours are 9.47
01Hours 57minutes there total hours are 1.95

Thanks again karl
KARL DEWEY said:
This will do it --
Worked: DateDiff("h", [IN_TIME], [OUT_TIME]) & "Hours " & (DateDiff("n",
[IN_TIME], [OUT_TIME])) Mod 60 & "Minutes"

--
KARL DEWEY
Build a little - Test a little


Mike said:
My query has an IN_TIME and an OUT_TIME with the date and time in same field
like shown below
how can i figure out the actual time that employee has worked
IN_TIME
12/1/2006 8:47:03 AM
OUT_TIME
12/1/2006 6:52:13 PM

Thanks for any help
Click to expand...
Click to expand...
 
G

Guest

Try this --
Worked: DateDiff("n", [IN_TIME], [OUT_TIME]) /60

--
KARL DEWEY
Build a little - Test a little


Mike said:
Ty Karl
That works
I should have gave alittle more info. Im needing something like if
they worked 09Hours 28minutes there total hours are 9.47
01Hours 57minutes there total hours are 1.95

Thanks again karl
KARL DEWEY said:
This will do it --
Worked: DateDiff("h", [IN_TIME], [OUT_TIME]) & "Hours " & (DateDiff("n",
[IN_TIME], [OUT_TIME])) Mod 60 & "Minutes"

--
KARL DEWEY
Build a little - Test a little


Mike said:
My query has an IN_TIME and an OUT_TIME with the date and time in same field
like shown below
how can i figure out the actual time that employee has worked
IN_TIME
12/1/2006 8:47:03 AM
OUT_TIME
12/1/2006 6:52:13 PM

Thanks for any help
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thank you Karl
Im actualy usnig access to get my SQL to be able to use in excel
the "n" i had to do ""n"" . But is workng great thanks again.
Mike

KARL DEWEY said:
Try this --
Worked: DateDiff("n", [IN_TIME], [OUT_TIME]) /60

--
KARL DEWEY
Build a little - Test a little


Mike said:
Ty Karl
That works
I should have gave alittle more info. Im needing something like if
they worked 09Hours 28minutes there total hours are 9.47
01Hours 57minutes there total hours are 1.95

Thanks again karl
KARL DEWEY said:
This will do it --
Worked: DateDiff("h", [IN_TIME], [OUT_TIME]) & "Hours " & (DateDiff("n",
[IN_TIME], [OUT_TIME])) Mod 60 & "Minutes"

--
KARL DEWEY
Build a little - Test a little


:

My query has an IN_TIME and an OUT_TIME with the date and time in same field
like shown below
how can i figure out the actual time that employee has worked
IN_TIME
12/1/2006 8:47:03 AM
OUT_TIME
12/1/2006 6:52:13 PM

Thanks for any help
Click to expand...
Click to expand...
Click to expand...
 
M

Matthew Hinkle

Karl, Mike,
Sorry to butt in here.
Karl, this solution works great for me.
Except, What does the "Mod 60" do?

I am using this without the "Hours" and "Minutes" labels but added a colon
to make the results look like time, however if the result is an even number
(i.e. 7 hrs) it displays as 7:0 instead of 7:00...

Mine looks like this:
Total Time: DateDiff("h",[Time IN],[Time OUT]) & ":" & (DateDiff("n",[Time
IN],[Time OUT])) Mod 60


Any suggestions for this?



KARL DEWEY said:
This will do it --
Worked: DateDiff("h", [IN_TIME], [OUT_TIME]) & "Hours " & (DateDiff("n",
[IN_TIME], [OUT_TIME])) Mod 60 & "Minutes"

--
KARL DEWEY
Build a little - Test a little


Mike said:
My query has an IN_TIME and an OUT_TIME with the date and time in same field
like shown below
how can i figure out the actual time that employee has worked
IN_TIME
12/1/2006 8:47:03 AM
OUT_TIME
12/1/2006 6:52:13 PM

Thanks for any help
Click to expand...
Click to expand...
 
J

John Spencer

Total Time: DateDiff("h",[Time IN],[Time OUT]) & ":" &
Format((DateDiff("n",[Time IN],[Time OUT])) Mod 60, "00")

Mod 60 divides by 60 and returns the remainder - so 365 mod 60 returns 5

In your case that eliminates the 60 minute groups that have been used to
give you the number of hours.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Matthew Hinkle said:
Karl, Mike,
Sorry to butt in here.
Karl, this solution works great for me.
Except, What does the "Mod 60" do?

I am using this without the "Hours" and "Minutes" labels but added a colon
to make the results look like time, however if the result is an even
number
(i.e. 7 hrs) it displays as 7:0 instead of 7:00...

Mine looks like this:
Total Time: DateDiff("h",[Time IN],[Time OUT]) & ":" & (DateDiff("n",[Time
IN],[Time OUT])) Mod 60


Any suggestions for this?



KARL DEWEY said:
This will do it --
Worked: DateDiff("h", [IN_TIME], [OUT_TIME]) & "Hours " & (DateDiff("n",
[IN_TIME], [OUT_TIME])) Mod 60 & "Minutes"

--
KARL DEWEY
Build a little - Test a little


Mike said:
My query has an IN_TIME and an OUT_TIME with the date and time in same
field
like shown below
how can i figure out the actual time that employee has worked
IN_TIME
12/1/2006 8:47:03 AM
OUT_TIME
12/1/2006 6:52:13 PM

Thanks for any help
Click to expand...
Click to expand...
Click to expand...
 
M

Matthew Hinkle

Thank you so much John!!
I altered the custom field to show both hours and minutes in 2-digit format
(hh:mm) with the input you have given me, like thus:

Total Time: Format((DateDiff("h",[Time IN],[Time OUT])) Mod 24, "00") &
":" & Format((DateDiff("n",[Time IN],[Time OUT])) Mod 60, "00")

Thanks again for everyone's help!!


John Spencer said:
Total Time: DateDiff("h",[Time IN],[Time OUT]) & ":" &
Format((DateDiff("n",[Time IN],[Time OUT])) Mod 60, "00")

Mod 60 divides by 60 and returns the remainder - so 365 mod 60 returns 5

In your case that eliminates the 60 minute groups that have been used to
give you the number of hours.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Matthew Hinkle said:
Karl, Mike,
Sorry to butt in here.
Karl, this solution works great for me.
Except, What does the "Mod 60" do?

I am using this without the "Hours" and "Minutes" labels but added a colon
to make the results look like time, however if the result is an even
number
(i.e. 7 hrs) it displays as 7:0 instead of 7:00...

Mine looks like this:
Total Time: DateDiff("h",[Time IN],[Time OUT]) & ":" & (DateDiff("n",[Time
IN],[Time OUT])) Mod 60


Any suggestions for this?



KARL DEWEY said:
This will do it --
Worked: DateDiff("h", [IN_TIME], [OUT_TIME]) & "Hours " & (DateDiff("n",
[IN_TIME], [OUT_TIME])) Mod 60 & "Minutes"

--
KARL DEWEY
Build a little - Test a little


:

My query has an IN_TIME and an OUT_TIME with the date and time in same
field
like shown below
how can i figure out the actual time that employee has worked
IN_TIME
12/1/2006 8:47:03 AM
OUT_TIME
12/1/2006 6:52:13 PM

Thanks for any help
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Number of Staff per Hour Clocked in 17
Punch time query to calculate total work time 5
Help Build Query 5
Query Help 5
Order Query By TIME HELP? 4
If date and user name same, return one record. 10
Help with update query UDF 1
Query to return complete record with max cost within grouping... 6

Top