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

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 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" & 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 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.

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

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

Typo

Column F is IN
Column H is OUT

H

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?

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

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

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

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.

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.

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!

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.

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.

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.

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

Howard