Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data timesheet makeover
Hello there!
I have a time sheet with follwing data of worked hours: Column A Column B Column C Column D Column E Column F Column G Column H EmployeeNș Day TimeIn1 TimeOut1 TimeIn2 TimeOut2 TimeIn3 TimeOut3 800001 2008-12-01 08:00 08:50 09:10 13:45 14:30 17:00 800001 2008-12-02 10:45 12:50 13:40 18:00 - - 800001 2008-12-03 0 0 0 0 0 0 800002 2008-12-01 13:55 14:30 14:45 17:00 - - 800002 2008-12-01 19:05 21:00 21:30 22:00 - - I need to create a data makeover to resume the data from columns C-H into the following new format (into a new sheet): Column A Column B Column C Column D EmployeeNș Day HourRange WorkedMinutes 800001 2008-12-01 8-9 50 800001 2008-12-01 9-10 50 800001 2008-12-01 10-11 60 800001 2008-12-01 11-12 60 800001 2008-12-01 12-13 60 800001 2008-12-01 13-14 45 etcetera What formula can I use to calculate "HourRange" and WorkedMinutes based on the data in columns C to H?(carefull: not all columns have to have data. 0 Means day of for example. "-" means there is no TimeIn or TimeOut data). Kind regards for your help! Martin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data timesheet makeover
The problem is too complicated to do with standard worksheet functions. It
can be done with a UDF shown below. Call with Formula WorkMinutes in column D of the summary sheet which corresponds to this entry 800001 2008-12-01 8-9 5 =workMinutes(Sheet1!A$1:H$100,A2,B2,C2) Sheet 1 is the table with all employees Time In and time Out data. the code assume a hearer row so it skips Row 1. A2 = 800001 B2 = 2008-12-01 C2 = 8-9 Function WorkMinutes(Target As Range, _ Employee As Long, _ DateWorked As Date, _ HoursWorked As String) WorkMinutes = 0 Hours = Split(HoursWorked, "-") 'convert Start and End time to fraction of a day StartTime = Val(Hours(0)) / 24 Endtime = Val(Hours(1)) / 24 'Skip Header Row For RowCount = 2 To Target.Rows.Count Set TimeRow = Target.Rows(RowCount) If TimeRow.Cells(1, 1) = Employee And _ Int(TimeRow.Cells(1, 2)) = Int(DateWorked) Then For ColCount = 3 To TimeRow.Columns.Count Step 2 If TimeRow.Cells(1, ColCount) < "-" And _ TimeRow.Cells(1, ColCount + 1) < "-" Then TimeIn = TimeRow.Cells(1, ColCount) TimeOut = TimeRow.Cells(1, ColCount + 1) 'check if time period is withing working period If TimeIn <= Endtime And TimeOut = StartTime Then If TimeIn <= StartTime And _ TimeOut = StartTime Then TimeIn = StartTime End If If TimeOut = Endtime And _ TimeIn <= Endtime Then TimeOut = Endtime End If WorkMinutes = WorkMinutes + _ (TimeOut - TimeIn) * (24 * 60) End If End If Next ColCount End If Next RowCount End Function "Martin Los" wrote: Hello there! I have a time sheet with follwing data of worked hours: Column A Column B Column C Column D Column E Column F Column G Column H EmployeeNÂș Day TimeIn1 TimeOut1 TimeIn2 TimeOut2 TimeIn3 TimeOut3 800001 2008-12-01 08:00 08:50 09:10 13:45 14:30 17:00 800001 2008-12-02 10:45 12:50 13:40 18:00 - - 800001 2008-12-03 0 0 0 0 0 0 800002 2008-12-01 13:55 14:30 14:45 17:00 - - 800002 2008-12-01 19:05 21:00 21:30 22:00 - - I need to create a data makeover to resume the data from columns C-H into the following new format (into a new sheet): Column A Column B Column C Column D EmployeeNÂș Day HourRange WorkedMinutes 800001 2008-12-01 8-9 50 800001 2008-12-01 9-10 50 800001 2008-12-01 10-11 60 800001 2008-12-01 11-12 60 800001 2008-12-01 12-13 60 800001 2008-12-01 13-14 45 etcetera What formula can I use to calculate "HourRange" and WorkedMinutes based on the data in columns C to H?(carefull: not all columns have to have data. 0 Means day of for example. "-" means there is no TimeIn or TimeOut data). Kind regards for your help! Martin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data timesheet makeover
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data timesheet makeover
Isn't a UDF a formula?
"Herbert Seidenberg" wrote: Since you asked for formulas.... Excel 2007: http://www.savefile.com/files/1860236 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data timesheet makeover
Joel:
This (UDF)-formula works perfect! Many thanks for your help. Regards Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
timesheet | Excel Discussion (Misc queries) | |||
Daily Timesheet data collection | New Users to Excel | |||
Timesheet | Excel Discussion (Misc queries) | |||
TIMESHEET | Excel Worksheet Functions | |||
Help with timesheet please | Excel Worksheet Functions |