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]

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.

 

[felixstraube]

Hi lilicile.
As you know datetime in excel is just a number where the unit represent a day and the decimal part the time. That is 1 hour is 1/24 = 0,0416666666666667. So 2024-02-14 01:00 becomes 45336,0416666667 for example. One minute is 1/(24*60) an so forth. You can read more about this here: How Dates Work in Excel - The Calendar System Explained + Video - Excel Campus

So having this in mind, we take starttime (which is date and time actually) and the endtime and subtract them endtime-strarttime. With gives me the time difference between them. Then we divide that by the time of 1 minute, so the result is the number of minutes from start to end (n in the formula).
Then we create a sequence of n datetimes starting at starttime with increment of 1 minute. So we get something like this:

Book1
	A	B	C	D
1	StartTime	2024-02-14 10:00		Minute Sequence (ms)
2	EndTime	2024-02-14 10:05		2024-02-14 10:00
3				2024-02-14 10:01
4	n	5		2024-02-14 10:02
5				2024-02-14 10:03
6				2024-02-14 10:04
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=SEQUENCE(B4,,B1,TIME(0,1,0))
B4	B4	=(B2-B1)/TIME(0,1,0)
Dynamic array formulas.

that is one datetime for every minute from start to end (ms in the formula). (Of course there can be a much larger number of minutes).
Then we extract the day taking only the integer part;

d, INT(ms),

and the time

t, VALUE(TEXT(ms-d, "hh:mm")),

Then we check if it is a working day

workingDay, WORKDAY(d-1,1,Holidays)=d,

"Holidays" is the table in which you need to enter your holidays days.

Then with

workingHourMonThu, (wd>=1)*(wd<=4)*(t>=monThuStart)*(t<monThuEnd),

we first check if it is day from monday to thurday and then if the time is between the workinghours start and end time.
then in the next line we do the same for friday (because in this case we have different working hours on friday).

Then we also exclude the minutes that are between the breaks
And last we use filter to filter out ms on all of the above criteria and filter will give us a list of minutes that are on a working day and in working hours and not a break time.
Then we sum them up and multiply them by TIME(0,1,0) (the time of one minute) and we get the total worked time.>

Hope this makes sense. If not, let me know

 

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