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

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.


1705622300526.png


Excel Formula:
=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.

1705622514526.png
 
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)
 
Upvote 1
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
You are welcome, and thanks for the feedback!!!

Try this:

ProductionTimeCalculation.xlsx
ABCDEFGHIJKLMN
1
2
3
4
5JobResultWorking HoursHolidays
6Start DateStart Time1:00StartEnd06/02/2024
709/02/202412:00Mon-Thu07:0016:0007/02/2024
8End DateEnd TimeFri07:0014:45
909/02/202413:30
10
11Breaks
1200:00StartEnd
13OvertimeMon-Thu Break 110:0010:15
14StartEndMon-Thu Break 213:0013:30
15Fri Break 109:3009:45
16Fri Break 212:3013:00
17
Sheet1
Cell Formulas
RangeFormula
F6F6=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)
C12C12=SUM(C15:C100-B15:B100)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,568
Messages
6,179,572
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