Calculating work hours between two datetimes

Hi!

I have datetimes in ranges I2# and J2#. I need to sum work time within the duration of these datetimes with a formula that creates a spill range.

The shift patterns are described in the table called �Tab_shift_pattern� with the following columns:

�Day of week� = the days of the week in text that cycles through Monday to Sunday repeatedly. The seven rows 2 to 8 (seven rows) are set aside for a certain period where a factory works according to a specific shift pattern that is described in the table on these rows. Then, at a later time, the shift pattern may change, and so the next seven rows (9 to 15) contain the setup for this period. And so on. There are therefore several blocks of seven days beneath each other in this column.

�Start date of shift pattern� = This column gives the first date that a certain shift pattern was implemented. So for the first seven rows, this column will contain the same date in each of the rows. Then, for the next seven rows, the start date of the next shift pattern will be there for all of those seven rows. Etc. depending on how many seven-row blocks we have.

�End date of shift pattern� = This column gives the last date that a certain shift pattern was being followed. So for the first seven rows, this column will contain that same date in each of the rows. Then, for the next seven rows, the end date of the next shift pattern will be there for all of those seven rows. Etc. depending on how many seven-row blocks we have. Thus, the start and end date where a certain shift pattern is valid will be entered outside each of the days of the week from Monday to Sunday. Then, a new block of seven days comes with a new period and so forth.

�Weekday� = this column contains numbers from 1 to 7 indicating the day of week, similar to what we have in text form in �Day of week�.

�Work time start� = this column contains the start time of day when a shift start for a particular weekday. So for each of the seven rows in the block for a certain shift pattern there may be different times for �Work time start� depending on which weekday it is.

�Work time end� = this column contains the end time of day when a shift ends for a particular weekday. So for each of the seven rows in the block for a certain shift pattern there may be different times for �Work time end� depending on which weekday it is.

I have uploaded an Excel workbook that shows the data. Note: The start datetimes of I2# may fall within a different shift pattern block than the end datetimes J2#, but will most of the time be in the same shift pattern block.

Any help is appreciated!

Best regards,
Marbleking

One way:

Please try in K2 and copy down:

Please Login or Register  to view this content.

Please try:

Please Login or Register  to view this content.