Production time calculation within the working day accounting for breaks

a13ks · Jan 18, 2024

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 · Jan 22, 2024

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 · Jan 23, 2024

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 · Jan 24, 2024

Happy to help. Thanks for the feedback.

lilicile · Feb 13, 2024

felixstraube said:
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 · Feb 14, 2024

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;

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

lilicile · Feb 15, 2024

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 · Feb 15, 2024

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

Production time calculation within the working day accounting for breaks

a13ks

New Member

felixstraube

Active Member

Excel Facts

a13ks

New Member

felixstraube

Active Member

lilicile

New Member

felixstraube

Active Member

lilicile

New Member

felixstraube

Active Member

Similar threads

Forum statistics

Share this page

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)

Production time calculation within the working day accounting for breaks

New Member

Active Member

Excel Facts

New Member

Active Member

New Member

Active Member

New Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock