franciscloete1
New Member
- Joined
- Aug 23, 2018
- Messages
- 6
Hope someone can help.
I have a set of data like this bellow (data1) I need to know what is the total amount of hours per week - I have 10 000 lines with different sets of times (data2). I need to get per line the total of hours per schedule times. as seen some of the lines have multiple times in them
Please some advice on this calculation.
data1
data2
I have a set of data like this bellow (data1) I need to know what is the total amount of hours per week - I have 10 000 lines with different sets of times (data2). I need to get per line the total of hours per schedule times. as seen some of the lines have multiple times in them
Please some advice on this calculation.
data1
MO | TUE | WED | THU | FRI | SAT | SUN |
2020/04/06 | 2020/04/07 | 2020/04/08 | 2020/04/09 | 2020/04/10 | 2020/04/11 | 2020/04/12 |
OFF | OFF | 18:00-06:00 | 18:00-06:00 | 18:00-06:00 | OFF | OFF |
05:30-15:30 | 05:30-15:30 | 05:30-15:30 | 05:30-15:30 | 05:30-13:30 | OFF | OFF |
data2
Schedule times | IN | Out | Lunch | Total hours |
05:00A | 00:00 | |||
05:30-15:30 | 05:30 | 15:30 | 01:00 | 09:00 |
05:30A | 00:00 | |||
06:00-15:00 | 06:00 | 17:00 | 01:00 | 10:00 |
06:00-16:00 | 06:00 | 16:00 | 01:00 | 09:00 |
06:00-18:00 | 06:00 | 18:00 | 01:15 | 10:45 |
06:30-15:30 | 06:30 | 15:30 | 01:00 | 08:00 |
06:30-16:00 | 06:30 | 16:00 | 01:00 | 08:30 |
06:30-16:30 | 06:30 | 16:30 | 01:00 | 09:00 |
06:30-17:00 | 06:30 | 17:00 | 01:00 | 09:30 |
07:00-15:30 | 07:00 | 15:30 | 01:00 | 07:30 |
07:00-16:00 | 07:00 | 16:00 | 01:00 | 08:00 |
07:00-16:30 | 07:00 | 16:30 | 01:00 | 08:30 |
07:00-17:00 | 07:00 | 17:00 | 01:00 | 09:00 |
07:00-17:30 | 07:00 | 17:30 | 01:00 | 09:30 |
07:00A | 00:00 | |||
07:30-16:00 | 07:30 | 16:00 | 01:00 | 07:30 |
07:30-17:00 | 07:30 | 17:00 | 01:00 | 08:30 |
09:00-18:00 | 09:00 | 18:00 | 01:00 | 08:00 |
09:30A | 00:00 | |||
18:00-06:00 | 18:00 | 06:00 | 01:15 | 10:45 |
9:30-18:00 | 09:30 | 18:00 | 01:00 | 07:30 |