Production time calculation within the working day accounting for breaks

[a13ks]

Good day to you all,

I'm looking to calculate the time taken for an installation.
Working hours are 07:00 to 16:00 Monday to Thursday & 07:00 to 14:45 on Friday.
I wish to be able to include in the calculation break times which are 10:00 to 10:15 & 13:00 to 13:30.

Cell B7 = Start Date C7 = Start Time
Cell B9 = End Date C9 = End Time

=IF(C9="","",(NETWORKDAYS((B7+C7),(B9+C9))-1)*(End_Time-Start_Time)+IF(NETWORKDAYS((B9+C9),(B9+C9)),MEDIAN(MOD((B9+C9),1),End_Time,Start_Time),End_Time)
-MEDIAN(NETWORKDAYS((B7+C7),(B7+C7))*MOD((B7+C7),1),End_Time,Start_Time))

I am managing to get the formula above to work if the hours for Mon to Fri were 07:00 to 16:00 but can not figure out how to then alter Friday hours and to deduct the breaks.

I modified it (below) to try and calculate for the breaks but when testing, I set the start at 18th Jan 07:00 and finish at 18th Jan 16:00 it returns 9 hours (not deducting the 45 mins for breaks) but if I change the finish time to the next day 19th Jan 07:00 it returns 8hr 15 mins!

=IF(C9="","",(NETWORKDAYS((B7+C7),(B9+C9))-1)*(End_Time-Start_Time+((Break_Start-Break_End)+(Lunch_Start-Lunch_End)))+IF(NETWORKDAYS((B9+C9),(B9+C9)),MEDIAN(MOD((B9+C9),1),End_Time,Start_Time),End_Time)
-MEDIAN(NETWORKDAYS((B7+C7),(B7+C7))*MOD((B7+C7),1),End_Time,Start_Time))

I ideally I'd like to factor in overtime too but would be over the moon if I could get this to work.

PLEASE HELP ME!!!!!

 

[a13ks]

Whilst you are on a roll....

How could I adapt that to calculate the scheduled completion time?
Using the start date and time if an installation was scheduled to take X working hours (X = Cell AB3) again taking into consideration the working day and break times.

 

[felixstraube]

How about this?:
the calculation is in 5 min intervals. And it will work correctly if the end date isn't more than 20 days after the starting date. I added this restrictions to make the formula work faster.

Let me know if that works for you.

ProductionTimeCalculation.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	Job				Result - End datetime			Working Hours				Holidays
3	Start Date	Start Time			2024-02-20 12:15				Start	End		06/02/2024
4	05/02/2024	12:00						Mon-Thu	07:00	16:00		07/02/2024
5								Fri	07:00	14:45
6	Job duration
7	72:00
8								Breaks
9									Start	End
10								Mon-Thu Break 1	10:00	10:15
11								Mon-Thu Break 2	13:00	13:30
12								Fri Break 1	09:30	09:45
13								Fri Break 2	12:30	13:00
14
15
Sheet1 (2)

Cell Formulas
Range	Formula
E3	E3	=LET(startTime,$A$4+$B$4,endTime, startTime+20, monThuStart,$I$4,monThuEnd,$J$4, friStart,$I$5,friEnd,$J$5, monThuBreak1Start,$I$10,monThuBreak1End,$J$10, monThuBreak2Start,$I$11,monThuBreak2End,$J$11, friBreak1Start,$I$12,friBreak1End,$J$12, friBreak2Start,$I$13,friBreak2End,$J$13, targetMin, ROUND($A$7/TIME(0,5,0),0)+1, n, (endTime-startTime)/TIME(0,5,0), ms, SEQUENCE(n,,startTime,TIME(0,5,0)), d, INT(ms), t, VALUE(TEXT(ms-d,"hh:mm")), wd, WEEKDAY(d,2), workingDay, WORKDAY(d-1,1,Holidays)=d, workingHourMonThu,(wd>=1)*(wd<=4)*(t>=monThuStart)*(t<monThuEnd)*NOT((t>=monThuBreak1Start)*(t<monThuBreak1End)+(t>=monThuBreak2Start)*(t<monThuBreak2End)), workingHourFri,(wd=5)*(t>=friStart)*(t<friEnd)*NOT((t>=friBreak1Start)*(t<friBreak1End)+(t>=friBreak2Start)*(t<friBreak2End)), mArray, workingDay * (workingHourMonThu + workingHourFri), cumulativeCount, DROP(REDUCE(0, mArray, LAMBDA(ini,x, VSTACK(ini, TAKE(ini,-1)+x))),1), INDEX(FILTER(ms, cumulativeCount=targetMin),1) )

 

[a13ks]

Hi and thank you once again. Absolutely perfect!!!

Although, I have just spent a very long time to figure out that I had the formatting set wrong. Still had it set [h]:mm instead of the date & time option. It took far too long for the penny to drop. Thanks again!

 

[felixstraube]

Happy to help. Thanks for the feedback.

 

[lilicile]

Here is a version that considers Holidays and Overtime.
Holidays are in a table called "Holidays".
For the overtime to work properly you would need to add a new line (with start and end datetime) for every day with overtime.

Here is the file to download:
ProductionTimeCalculation2.zip

ProductionTimeCalculation.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4									Working Hours				Holidays
5		Job				Result				Start	End		06/02/2024
6		Start Date	Start Time			19:00			Mon-Thu	07:00	16:00		07/02/2024
7		05/02/2024	07:00						Fri	07:00	14:45
8		End Date	End Time
9		08/02/2024	16:00
10									Breaks
11										Start	End
12			02:30						Break 1	10:00	10:15
13		Overtime							Break 2	13:00	13:30
14		Start	End
15		05/02/2024 16:00	05/02/2024 17:00
16		08/02/2024 16:00	08/02/2024 17:30
17
Sheet1

Cell Formulas
Range	Formula
F6	F6	=LET(startTime, $B$7+$C$7, endTime, $B$9+$C$9, monThuStart, $J$6, monThuEnd, $K$6, friStart, $J$7, friEnd, $K$7, break1Start, $J$12, break1End, $K$12, break2Start, $J$13, break2End, $K$13, n, (endTime-startTime)/TIME(0,1,0), ms, SEQUENCE(n,,startTime, TIME(0,1,0)), d, INT(ms), t, VALUE(TEXT(ms-d, "hh:mm")), wd, WEEKDAY(d,2), workingDay, WORKDAY(d-1,1,Holidays)=d, workingHourMonThu, (wd>=1)*(wd<=4)*(t>=monThuStart)*(t<monThuEnd), workingHourFri, (wd=5)*(t>=friStart)*(t<friEnd), break, (t>=break1Start)*(t<break1End) + (t>=break2Start)*(t<break2End), IFERROR(ROWS(FILTER(ms, workingDay * (workingHourMonThu + workingHourFri) * NOT(break))),0)*TIME(0,1,0)) + SUM(C15:C100-B15:B100)
C12	C12	=SUM(C15:C100-B15:B100)

Hi,
thank you for this, i have the same inquiries, just in different format.
I am trying to use your formula and adjust it to my need, but still abit confused on your logic, particularly on using the Sequence function.
Do you mind to explain about the logic you use to get this?
I already have the date and time in the same field, and all i need is only the number (of hours) in between the start and end time.
Really appreciate it, cheers.

 

[lilicile]

Hi felixstraube,

Really appreciate your effort for explaining, it's more make sense now.
Thank you so much. I'll try to use it on mine.

Cheers.

 

[felixstraube]

You are welcome. If you have any questions just ask.