sumproduct adding date to time range only works for same date

Hello

I need to sum up minutes, if any of the specified time stamp (workbreak) falls within a time range.
The specified time stamp cannot be hardcoded for the specific date. It needs to be only time as it is part of a standard work schedule.
The time range can span several dates and the time stamp must be counted for every passing date.

So far I have this, which works if both start and end time is on the same date:

=SUMPRODUCT((INT(A5)+$H$3:$H$4>=A5)*(INT(A6)+$H$3:$H$4<=A6)*$I$3:$I$4)

I've also tried sumifs, which also works if start and end time is on same date:
=SUMIFS($I$3:$I$4;$H$3:$H$4;">="&MOD(A5;1);$H$3:$H$4;"<="&MOD(A6;1))

A5 is start time
A6 is end time
H3:H4 is two timestamps which needs to be found
I3:I4 is minutes which needs to be added for each timestamp

The difference between the to methods is that with SUMPROCUCT I add the date to the time stamp. In the SUMIFS I remove the date from the start/end time, both to get the same format for time range and time stamp.

I'm working in Excel365

Can you help me, please?

Hello John

Thank you for your help. It got me at bit further, but not all the way.
Now the first two due dates are correct.
I believe your appoach is the right one. I may just have to add additional "if" statements.

I'm attaching my workbook again, now with my expected results.

Thank you JeteMc

Your formula worked perfectly when I used the expected due dates in column C. The only one not hitting the right time is monday 8th. This is not because of your formula though, but because friday has shorter working hours and thus no lunch break.
The shorter working hours is an entirely different problem which I'm trying to solve, but I'll leave that for another post.

However when I subsitute A24 in your formula with the formula calculating A24, I get the wrong result. I know why, but not how to solve it.
The formula to calculate A24 calculates the due date before breaks:

Formula:

Please Login or Register  to view this content.

but when the due date/time is between first and second break and adding the first break result in passing the second breaktime, the formula doesn't know that it needs to add the second break as well.
Ex: Feb 2nd due date/time is 11:57 before breaks. Adding 20 min results in 12:17, which means I've passed 2nd breaktime and needs to add another 25 min to get to 12:42 as my correct due date/time

This is my tongue twisting formula for cell A24 when its all added together (hope I got all the translations from danish to english excel language right):

Formula:

Please Login or Register  to view this content.

Hello JeteMc

You are right, I had my mind set on having it all in one formula. This is because I have to fit it into an existing production plan, which is shared with some of my collegues. I wanted the simplicity of just choosing the start date and the run time and letting Excel do the rest automatically.
I am aware that it would be a very complex formula and especially the difference in number of breaks would be difficult to manage. I also considered using VBA.

I think I will work with the existing production plan as a result sheet and create an intermediate sheet for the calculations, using your method of splitting it into several columns.

Thank you for helping me find a simple formula for calculating the breaks, and helping me look beyond the one-cell formula. Both your answers were very helpful.

I'll mark your first answer (#4) as solved as it answers my initial question.

Hello again JeteMc

Now I've had time to play with your solution for several columns and I've found 2 problems
1st: column F doesn't get it quite right on fridays. It works fine for the first friday, but the second friday gets it wrong (counts 3:25 h:m to much (the difference between 15:15 and 11:50))
2nd: column J returns FALSE when SUM columns H+I passes end work time.

Don't know if I should continue in this tread or start a new thread as it seems to be a different issue than my original one.

Hi JeteMc

As I see it we should be able to drag down continuously without problems, but as you can see some fridays are incorrect.
If you change A2 to 01-02-2021 07:00 then you will see, that several fridays end after 11:50, which to me indicates that the formula in column F is incorrect.
I should be able to pick any day and time of the week as my starting point and continue for as long as I like, but right now its limited to starting monday morning at 6:30 and only running for 2 weeks.

I've tried changing < to > and vise versa in the formula in column F and working with monday and thursday as well, but that doesn't work.

I'm quite stuck

p.s I changed the breaks from integer to time format, but that doesn't affect the formula in column F

Hi JeteMc

I really appreciate your help. I believe we are almost there.

Your formula for column F and G worked perfectly.

Though, when I changed the first start time randomly, I still had problems with some fridays ending after 11:50 in the intermediate times and the final due date.
I fixed this by adding extra columns using the same formula as in column F.

My only problem now (I hope) is calculating break time in column N.
I have a hard time understanding exactly how the "break time" formula works in general. Maybe that is why I don't know how to fix it.

Hello JeteMc

That did the final trick. Now it's working all over.
Thank you som much for your help

I've attached the final workbook for others who have a similar issue.