sumproduct adding date to time range only works for same date

[mlan]

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?

[JohnTopley]

Please can you add expected results to your file.

My attempt ..

=SUMPRODUCT((INT(A7)+$H$3:$H$4>=A7)*(INT(A8)+$H$3:$H$4<=A8)*$I$3:$I$4)+(IF(INT(A8)>INT(A7),SUMPRODUCT((INT(A8)+$H$3:$H$4<=A8)*$I$3:$I$4),0))

[mlan]

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.

[JeteMc]

Try pasting the following into cell B23 and dragging the fill handle down:

=IF(INT(A23)=INT(A24),SUMPRODUCT((H$3:H$4>=MOD(A23,1))*(H$3:H$4<=MOD(A24,1))*I$3:I$4),SUMPRODUCT((INT(A23)+$H$3:$H$4>=A23)*$I$3:$I$4+(INT(A24)+$H$3:$H$4<=A24)*$I$3:$I$4))

Note that a few of the results don't match those that were manually placed in B23:B33 however the results seem correct. For example between Feb 3 at 10:35 and Feb 4 at 8:34 there is only one break (Lunch on the 3rd) so the result seems as if it should be 25 in cell B26.
Let us know if you have any questions.

[mlan]

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:

=WORKDAY(A23,CEILING(($B$2/24+MOD(A23,1)-$F$2)/($F$7-$F$2),1)-1,Holidays!$B$4:$B$35)+MOD(A23,1)+$B$2/24-CEILING(MOD(A23,1)+$B$2/24-$F$2,$F$7-$F$2)+$F$7-$F$2

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):

=WORKDAY(A23,CEILING(($B$2/24+MOD(A23,1)-$F$2)/($F$7-$F$2),1)-1,Holidays!$B$4:$B$35)+MOD(A23,1)+$B$2/24-CEILING(MOD(A23,1)+$B$2/24-$F$2,$F$7-$F$2)+$F$7-$F$2+TIME(0,IF(INT($A23)=INT(WORKDAY(A23,CEILING(($B$2/24+MOD(A23,1)-$F$2)/($F$7-$F$2),1)-1,Holidays!$B$4:$B$35)+MOD(A23,1)+$B$2/24-CEILING(MOD(A23,1)+$B$2/24-$F$2,$F$7-$F$2)+$F$7-$F$2),SUMPRODUCT(($H$3:$H$4>=MOD($A23,1))*($H$3:$H$4<=MOD((WORKDAY(A23,CEILING(($B$2/24+MOD(A23,1)-$F$2)/($F$7-$F$2),1)-1,Holidays!$B$4:$B$35)+MOD(A23,1)+$B$2/24-CEILING(MOD(A23,1)+$B$2/24-$F$2,$F$7-$F$2)+$F$7-$F$2),1))*$I$3:$I$4),SUMPRODUCT((INT($A23)+$H$3:$H$4>=$A23)*$I$3:$I$4+(INT(WORKDAY(A23,CEILING(($B$2/24+MOD(A23,1)-$F$2)/($F$7-$F$2),1)-1,Holidays!$B$4:$B$35)+MOD(A23,1)+$B$2/24-CEILING(MOD(A23,1)+$B$2/24-$F$2,$F$7-$F$2)+$F$7-$F$2)+$H$3:$H$4<=(WORKDAY(A23,CEILING(($B$2/24+MOD(A23,1)-$F$2)/($F$7-$F$2),1)-1,Holidays!$B$4:$B$35)+MOD(A23,1)+$B$2/24-CEILING(MOD(A23,1)+$B$2/24-$F$2,$F$7-$F$2)+$F$7-$F$2))*$I$3:$I$4)),0)

[JeteMc]

With respect, I feel that the reason this isn't working is the attempt to roll everything into one formula.
Please see if the formulas used in the range E23:L34 are producing the correct results.
For Start date (after the first one): =L23
For Fridays without considering breaks:

=IF(WEEKDAY(E23,1)=6,WORKDAY(E23,CEILING(($B$2/24+MOD(E23,1)-$G$2)/($G$7-$G$2),1)-1,Holidays!$B$4:$B$35)+MOD(E23,1)+$B$2/24-CEILING(MOD(E23,1)+$B$2/24-$G$2,$G$7-$G$2)+$G$7-$G$2,"")

For Friday breaks (preliminary):

=IF(F23="","",IF(INT(E23)=INT(F23),SUMPRODUCT((H$3>=MOD(E23,1))*(H$3<=MOD(F23,1))*I$3),SUMPRODUCT((INT(E23)+$H$3>=E23)*$I$3+(INT(F23)+$H$3<=F23)*$I$3)))

For Mon - Thu without considering breaks:

=IF(F23="",WORKDAY(E23,CEILING(($B$2/24+MOD(E23,1)-$F$2)/($F$7-$F$2),1)-1,Holidays!$B$4:$B$35)+MOD(E23,1)+$B$2/24-CEILING(MOD(E23,1)+$B$2/24-$F$2,$F$7-$F$2)+$F$7-$F$2,"")

For Mon - Thu breaks (preliminary):

=IF(G23="",IF(INT(E23)=INT(H23),SUMPRODUCT((H$3:H$4>=MOD(E23,1))*(H$3:H$4<=MOD(H23,1))*I$3:I$4),SUMPRODUCT((INT(E23)+$H$3:$H$4>=E23)*$I$3:$I$4+(INT(H23)+$H$3:$H$4<=H23)*$I$3:$I$4)),"")

For Preliminary Due Date:

=IF(H23<>"",IF(SUM(MOD(H23,1),I23/1440)<=F$7,SUM(H23,I23/1440)),IF(SUM(MOD(F23,1),G23/1440)<=G$7,SUM(F23,G23/1440)))

For Final Breaks:

=IF(H23<>"",IF(INT(H23)=INT(J23),SUMPRODUCT((H$3:H$4>=MOD(H23,1))*(H$3:H$4<=MOD(J23,1))*I$3:I$4),SUMPRODUCT((INT(H23)+$H$3:$H$4>=H23)*$I$3:$I$4+(INT(J23)+$H$3:$H$4<=J23)*$I$3:$I$4)),IF(INT(F23)=INT(J23),SUMPRODUCT((H$3>=MOD(F23,1))*(H$3<=MOD(J23,1))*I$3),SUMPRODUCT((INT(F23)+$H$3>=F23)*$I$3+(INT(J23)+$H$3<=J23)*$I$3)))

For Final Due Date: =SUM(J23,K23/1440)
Let us know if you have any questions.

[mlan]

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.

[mlan]

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.

[FlameRetired]

Hello

.....
I'm working in Excel365

Can you help me, please?

mlan please update your profile to reflect that.

[JeteMc]

I have added some columns N:P to check the results and each row displays a duration of 6:43 so that the sum of the 11 rows is 74:00.
Originally I dragged the formula down one row too far, so perhaps that is why it seems the second Friday is incorrect.
If that isn't the problem then please provide us a sample that illustrates where the formulas fail.
Note that it may be a good idea to put the total time and number of modules in cells (i.e. C1:D1) and then modify the formula for E24 and down to read: =IF(ROWS(A$1:A2)<=C$1,L23,"")
Please do not start a new thread, you can use the thread tools menu above your first post to remove the 'Solved' designation.

[mlan]

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

[JeteMc]

Try the following:
For F23 and down:

=IF(WEEKDAY(E23,1)=6,IF(SUM(MOD(E23,1),B$2/24)>G$7,SUM(WORKDAY(E23,1,Holidays!B$4:B$35),G$2,B$2/24-(G$7-MOD(E23,1))),SUM(E23,B$2/24)),"")

For G23 and down:

=IF(F23="","",TIME(0,SUM(IF(MOD(E23,1)<=H$3,I$3,0),IF(MOD(F23,1)>=H$4,SUM(I$3:I$4),IF(MOD(F23,1)>=H$3,I$3,0))),0))

Let us know if you have any questions.

[mlan]

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.

[JeteMc]

Try the following For N23 and down:

=IF(M23="","",TIME(0,SUM(IF(MOD(L23,1)<=O$3,P$3,0),IF(MOD(M23,1)>=O$4,SUM(P$3:P$4),IF(MOD(M23,1)>=O$3,P$3,0))),0))

Let us know if you have any questions.

[mlan]

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.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.