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

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

Data timesheet makeover
 

Since you asked for formulas....
Excel 2007:
http://www.savefile.com/files/1860236

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

Data timesheet makeover
 

Joel:

This (UDF)-formula works perfect!

Many thanks for your help.

Regards

Martin