Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum work hours to hours & nearest 10th of an hour
Typo Column F is IN Column H is OUT H |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
work hours with different hour prices | Excel Worksheet Functions | |||
Converting work hours to day/hours/minutes | Excel Discussion (Misc queries) | |||
Determining work hours between dates / hours | Excel Worksheet Functions | |||
total work hours for 24 hour on-call schedules | Excel Discussion (Misc queries) | |||
Apply a macro to all sheets - save me from hours and hours of work | Excel Programming |