Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Sum work hours to hours & nearest 10th of an hour

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Sum work hours to hours & nearest 10th of an hour

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.


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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Sum work hours to hours & nearest 10th of an hour

On Thursday, July 17, 2014 2:18:23 AM UTC-7, Claus Busch wrote:
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.




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

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Sum work hours to hours & nearest 10th of an hour


Typo

Column F is IN
Column H is OUT

H
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Sum work hours to hours & nearest 10th of an hour

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Sum work hours to hours & nearest 10th of an hour

On Thursday, July 17, 2014 2:47:28 AM UTC-7, L. Howard wrote:
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
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Sum work hours to hours & nearest 10th of an hour

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


please have a look:
https://onedrive.live.com/?cid=9378A...121822A3%21326
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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Sum work hours to hours & nearest 10th of an hour

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Sum work hours to hours & nearest 10th of an hour





if you cannot get it to work





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional


The formatting of column N seems to be a problem.

I formatted same as your example but get some strange returns.


https://www.dropbox.com/s/zpqiwcs3qb...and%20OUT.xlsm

Howard
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Sum work hours to hours & nearest 10th of an hour

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Sum work hours to hours & nearest 10th of an hour


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
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Sum work hours to hours & nearest 10th of an hour

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Sum work hours to hours & nearest 10th of an hour

On Thursday, July 17, 2014 5:11:22 AM UTC-7, Claus Busch wrote:
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.


Thanks, Claus.

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

Howard
  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Sum work hours to hours & nearest 10th of an hour

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)),R OUNDUP(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))*1 44,0)/144)
or look again in OneDrive


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Sum work hours to hours & nearest 10th of an hour


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)),R OUNDUP(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))*1 44,0)/144)

or look again in OneDrive





Regards

Claus B.


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

Howard
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
work hours with different hour prices [email protected] Excel Worksheet Functions 12 January 30th 14 12:03 AM
Converting work hours to day/hours/minutes WxmanPrice Excel Discussion (Misc queries) 3 October 20th 09 03:08 PM
Determining work hours between dates / hours Andrew Excel Worksheet Functions 3 July 30th 08 06:38 PM
total work hours for 24 hour on-call schedules Kate Excel Discussion (Misc queries) 1 February 7th 06 04:01 AM
Apply a macro to all sheets - save me from hours and hours of work Excel'ed Failures Excel Programming 3 January 30th 06 06:48 PM


All times are GMT +1. The time now is 11:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"