Data timesheet makeover

[Martin Los]

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

 

[Joel]

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

 

[Herbert Seidenberg]

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

 

[Joel]

Isn't a UDF a formula?

 

[Martin Los]

Joel:

This (UDF)-formula works perfect!

Many thanks for your help.

Regards

Martin