Sum work hours to hours & nearest 10th of an hour

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

L. Howard

On the active sheet that represents a given month of the year:

Column D = employee who has signed in
Column E = Date in
Column F = sign in time
Column G = Date out
Column H = sign out time

Sarah 7/16/2014 9:44 PM 7/16/2014 10:22 PM
Jaydee 7/16/2014 9:44 PM 7/16/2014 10:48 PM
Frank 7/16/2014 10:51 PM

Column M = A unique list of all employees.

Employees will be signed in and out several times.
Not every employee in column M will be signed in (some may not work that month)
Some will be signed in but not signed out when the code is run during the course of the month.

Trying to get this line to return all hours worked and minutes to the nearest 10th to column N next to employee name.

ie. every 6 minutes = .1 of an hour.


cM.Offset(, 1) = (cD.Offset(, 4) - cD.Offset(, 2)) + (cM.Offset(, 1))


So each time code is run, column N is cleared and all hours are updated.

What I have right now is values that pretty much don't mean anything to me, and some are negative.

Column N formatted as General until I know what it should be.

I would expect the code to be run on the active sheet so it could used for all months, with each month on a sheet.

Thanks,
Howard


Sub NameAndHours()

Dim Mname As Range, Dname As Range
Dim cM As Range, cD As Range
Dim i As Long

Set Mname = Range("M2:M" & Cells(Rows.Count, "M").End(xlUp).Row)
Set Dname = Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row)

For Each cM In Mname

For Each cD In Dname

If cM = cD Then

cM.Offset(, 1) = (cD.Offset(, 4) - cD.Offset(, 2)) + (cM.Offset(, 1))

End If

Next

Next

End Sub
 
Hi Howard,

Am Thu, 17 Jul 2014 00:35:07 -0700 (PDT) schrieb L. Howard:
On the active sheet that represents a given month of the year:

Column D = employee who has signed in
Column E = Date in
Column F = sign in time
Column G = Date out
Column H = sign out time

Sarah 7/16/2014 9:44 PM 7/16/2014 10:22 PM
Jaydee 7/16/2014 9:44 PM 7/16/2014 10:48 PM
Frank 7/16/2014 10:51 PM

Column M = A unique list of all employees.
Click to expand...

do you need a macro?

you can use formula in N1:
=IF(G1="","",SUMPRODUCT(($G$1:$G$100+$H$1:$H$100-($E$1:$E$100+$F$1:$F$100))*($D$1:$D$100=M1)))
and copy down


Regards
Claus B.
 
Hi Howard,



Am Thu, 17 Jul 2014 00:35:07 -0700 (PDT) schrieb L. Howard:












do you need a macro?



you can use formula in N1:

=IF(G1="","",SUMPRODUCT(($G$1:$G$100+$H$1:$H$100-($E$1:$E$100+$F$1:$F$100))*($D$1:$D$100=M1)))

and copy down





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional
Click to expand...

Hi Claus,

Yes, I believe a formula will be fine.

The one you offer up returns #VALUE error.

Could that be because it refers to columns with dates in then?

I tried to shift the column references in the formula but don't understand the SUMPRODUCT well enough to do that.

The times in is IN column F and the times OUT is in column H.

I'll keep working on it from my end.

Howard
 
Hi Howard,

Am Thu, 17 Jul 2014 02:45:01 -0700 (PDT) schrieb L. Howard:
The one you offer up returns #VALUE error.

Could that be because it refers to columns with dates in then?
Click to expand...

no, the formula is tested and it works.
I guess your data has wrong formats.
Can you send me the file?


Regards
Claus B.
 
Typo



Column F is IN

Column H is OUT



H
Click to expand...


Okay, fixed it by starting in row 2 instead if 1.

I get values like this in N, where the 0 is a employee not signed in. I don't understand the negative values???

What format would I use for column N to convert to hours + 10th's?

-41836.9135
0
0.041956019
0
0.041967593
0
-41836.93432
0
0.02650463


Howars
 
Hi Howard,

Am Thu, 17 Jul 2014 02:56:58 -0700 (PDT) schrieb L. Howard:
What format would I use for column N to convert to hours + 10th's?

-41836.9135
0
0.041956019
0
0.041967593
0
-41836.93432
0
0.02650463
Click to expand...

please have a look:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "WorkingTime"

In last row Jaydee starts on 18. July and ends on 19. July. Therefore I
included the date columns

If you can get it to work, please send me the file


Regards
Claus B.
 
Hi again,

Am Thu, 17 Jul 2014 12:01:53 +0200 schrieb Claus Busch:
If you can get it to work, please send me the file
Click to expand...

if you cannot get it to work


Regards
Claus B.
 
Hi Howard,

Am Thu, 17 Jul 2014 03:13:19 -0700 (PDT) schrieb L. Howard:
The formatting of column N seems to be a problem.
Click to expand...

the problem is that some have checked in but not checked out. So 0 is
substracted by CheckIn time and you get negative times.
You have to do it with macro.
Look again in OneDrive for
"Hours Worked by Time IN and OUT"

Delete the times in column N an run "Times"


Regards
Claus B.
 
You have to do it with macro.

Look again in OneDrive for

"Hours Worked by Time IN and OUT"



Delete the times in column N an run "Times"

Regards

Claus B.
Click to expand...

Mighty fine! That works very well!

Thanks much.

Howard
 
Hi Howard,

Am Thu, 17 Jul 2014 05:02:03 -0700 (PDT) schrieb L. Howard:
Mighty fine! That works very well!
Click to expand...

and here is a formula that works:
=SUMPRODUCT(($G$2:$G$100+$H$2:$H$100-$E$2:$E$100-$F$2:$F$100)*($H$2:$H$100<>"")*($D$2:$D$100=M2))


Regards
Claus B.
 
Hi Howard,



Am Thu, 17 Jul 2014 05:02:03 -0700 (PDT) schrieb L. Howard:






and here is a formula that works:

=SUMPRODUCT(($G$2:$G$100+$H$2:$H$100-$E$2:$E$100-$F$2:$F$100)*($H$2:$H$100<>"")*($D$2:$D$100=M2))





Regards

Claus B.
Click to expand...

Thanks, Claus.

I like the formula better in this case and it is working perfect.

Howard
 
Hi Howard,

Am Thu, 17 Jul 2014 09:59:04 -0700 (PDT) schrieb L. Howard:
I like the formula better in this case and it is working perfect.
Click to expand...

if you want to roundup to the next 10th minute try:
=IF(MINUTE(SUMPRODUCT(($G$2:$G$100+$H$2:$H$100-$E$2:$E$100-$F$2:$F$100)*($H$2:$H$100<>"")*($D$2:$D$100=M2)))=0,SUMPRODUCT(($G$2:$G$100+$H$2:$H$100-$E$2:$E$100-$F$2:$F$100)*($H$2:$H$100<>"")*($D$2:$D$100=M2)),ROUNDUP(SUMPRODUCT(($G$2:$G$100+$H$2:$H$100-$E$2:$E$100-$F$2:$F$100)*($H$2:$H$100<>"")*($D$2:$D$100=M2))*144,0)/144)
or look again in OneDrive


Regards
Claus B.
 
if you want to roundup to the next 10th minute try:

=IF(MINUTE(SUMPRODUCT(($G$2:$G$100+$H$2:$H$100-$E$2:$E$100-$F$2:$F$100)*($H$2:$H$100<>"")*($D$2:$D$100=M2)))=0,SUMPRODUCT(($G$2:$G$100+$H$2:$H$100-$E$2:$E$100-$F$2:$F$100)*($H$2:$H$100<>"")*($D$2:$D$100=M2)),ROUNDUP(SUMPRODUCT(($G$2:$G$100+$H$2:$H$100-$E$2:$E$100-$F$2:$F$100)*($H$2:$H$100<>"")*($D$2:$D$100=M2))*144,0)/144)

or look again in OneDrive





Regards

Claus B.
Click to expand...

What could be better than all those choices! Thanks a million Claus.

Howard
 
Back
Top