M
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
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