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!!!!!

 

[felixstraube]

Hi,
Hope this is what you need:
Please format the result cell as custom format with this format string "[h]:mm" to show hours and minutes (we need this format to show correctly a result with more than 24 hours). I can't do it right now because I'm on the web app.
I added the cells J6:K7 and J12:K13 to enter the working hours start and end and the breaks start and end. You can move them as needed. Or if you don't want them we can hardcode this values into the formula. Let me know what you think and I'll make the changes.

=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),
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),
x, IFERROR(ROWS(FILTER(ms, (workingHourMonThu + workingHourFri) * NOT(break))),0)*TIME(0,1,0),
x
)

here is the working file to download:

ProductionTimeCalculation.zip


I added another testing sheet. It seems that it is working fine. Please test it and let me know.
Another thing that we could add is a list of holidays that would be excluded from the time calculation. If you need that let me know also.

 

[felixstraube]

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)

 

[a13ks]

Thank you so much.

I'm just back at my PC and will be giving it a go straight away.

I'll update you and let you know how I get on.

 

[felixstraube]

I just realized that you don't show in your profile what version of excel you are working with.
The formula will only work with version 2021 or 365.
If you have a previous version we'll have to try another approach. Let me know.

 

[a13ks]

Absolutely SMASHED IT! Amazing, Thank you.

One thing I did forget to say was that the break times are different on a Friday 09:30 - 09:45 & 12:30 - 13:00. Is there anyway of working that into it?

 

[felixstraube]

Sure, let me just make that change.

 

[felixstraube]

You are welcome, and thanks for the feedback!!!

Try this:

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

Cell Formulas
Range	Formula
F6	F6	=LET(startTime,$B$7+$C$7,endTime,$B$9+$C$9, monThuStart,$J$7,monThuEnd,$K$7, friStart,$J$8,friEnd,$K$8, monThuBreak1Start,$J$13,monThuBreak1End,$K$13, monThuBreak2Start,$J$14,monThuBreak2End,$K$14, friBreak1Start,$J$15,friBreak1End,$K$15, friBreak2Start,$J$16,friBreak2End,$K$16, 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)*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)), IFERROR(ROWS(FILTER(ms, workingDay * (workingHourMonThu + workingHourFri) )),0)*TIME(0,1,0) ) + SUM(C15:C100-B15:B100)
C12	C12	=SUM(C15:C100-B15:B100)

 

[a13ks]

Amazing. Thank you!

What a legend!

 

[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.