Calculation advice times

[franciscloete1]

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

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​

 

[GlennUK]

Have you tried a PivotTable?

 

[franciscloete1]

Have you tried a PivotTable?

Yes I have but I need a total for the days allocated times via the lookup fields (data2)

 

[GlennUK]

Yes I have but I need a total for the days allocated times via the lookup fields (data2)

I see no days indicator in fields in data2 ... please explain.

 

[GlennUK]

Is this thread now dead?