Production time calculation within the working day accounting for breaks

a13ks

New Member
Joined
Jan 10, 2018
Messages
15
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!!!!!
 
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
ABCDEFGHIJKLM
1
2JobResult - End datetimeWorking HoursHolidays
3Start DateStart Time2024-02-20 12:15StartEnd06/02/2024
405/02/202412:00Mon-Thu07:0016:0007/02/2024
5Fri07:0014:45
6Job duration
772:00
8Breaks
9StartEnd
10Mon-Thu Break 110:0010:15
11Mon-Thu Break 213:0013:30
12Fri Break 109:3009:45
13Fri Break 212:3013:00
14
15
Sheet1 (2)
Cell Formulas
RangeFormula
E3E3=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) )
 
Upvote 0
Solution

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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!
 
Upvote 0
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
ABCDEFGHIJKLM
1
2
3
4Working HoursHolidays
5JobResultStartEnd06/02/2024
6Start DateStart Time19:00Mon-Thu07:0016:0007/02/2024
705/02/202407:00Fri07:0014:45
8End DateEnd Time
908/02/202416:00
10Breaks
11StartEnd
1202:30Break 110:0010:15
13OvertimeBreak 213:0013:30
14StartEnd
1505/02/2024 16:0005/02/2024 17:00
1608/02/2024 16:0008/02/2024 17:30
17
Sheet1
Cell Formulas
RangeFormula
F6F6=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)
C12C12=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.
 
Upvote 0
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
ABCD
1StartTime2024-02-14 10:00Minute Sequence (ms)
2EndTime2024-02-14 10:052024-02-14 10:00
32024-02-14 10:01
4n52024-02-14 10:02
52024-02-14 10:03
62024-02-14 10:04
Sheet1
Cell Formulas
RangeFormula
D2:D6D2=SEQUENCE(B4,,B1,TIME(0,1,0))
B4B4=(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;
Excel Formula:
d, INT(ms),
and the time
Excel Formula:
t, VALUE(TEXT(ms-d, "hh:mm")),

Then we check if it is a working day
Excel Formula:
workingDay, WORKDAY(d-1,1,Holidays)=d,
"Holidays" is the table in which you need to enter your holidays days.

Then with
Excel Formula:
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
 
Upvote 1
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.
 
Upvote 0

Forum statistics

Threads
1,224,568
Messages
6,179,586
Members
452,927
Latest member
whitfieldcraig

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top