Working Hours Taken to Complete a Task

[reberryjr]

I'm trying to calculate the amount of time taken to complete a task. The data I receive is a date/time stamp format. I'm wanting to establish how many working hours & minutes it takes to complete each task. I've been googling and trying different things for about 6 hours now, and I can't seem to find any articles that fit my scenario and/or make sense. Here's a small sample of the data I receive, with calculations shown in the Formula Used column and the results of said calculations in the Hours & Minutes column.

 

[AhoyNC]

Like this?
Use custom formatting [h]:mm in your Hour & Minute column.

Book1
	A	B	C
1	Start	End	Hrs & Min
2	7/30/2024 14:08	7/30/2024 14:52	0:44
3	6/25/2024 15:33	7/5/2024 7:41	232:08
4	6/25/2024 15:33	7/5/2024 9:35	234:02
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=B2-A2

 

[reberryjr]

Like this?
Use custom formatting [h]:mm in your Hour & Minute column.

Book1
	A	B	C
1	Start	End	Hrs & Min
2	7/30/2024 14:08	7/30/2024 14:52	0:44
3	6/25/2024 15:33	7/5/2024 7:41	232:08
4	6/25/2024 15:33	7/5/2024 9:35	234:02
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=B2-A2

View attachment 115309

Unfortunately, that doesn't help me with reducing the total hours & minutes down to just working hours. Meaning, 6/25/24 - 7/5/24 is 7 working days. 8 hours per day * 7 days = 56 hours, which is waaaay under the 230+ hour results

 

[Ezguy4u]

reberry How about this. You will get the number of working days, notice July 4th holiday, but those hours and minutes would be extra credit. We would need to talk more about hours and minutes.

24-08-12.xlsx
	A	B	C	D
1	Start	End
2	7/30/2024 14:08	7/30/2024 14:52		1
3	6/25/2024 15:33	7/5/2024 7:41		8
4	6/25/2024 15:33	7/5/2024 9:35		8
5
6	7/4/2024
Data

Cell Formulas
Range	Formula
D2:D4	D2	=NETWORKDAYS(A2,B2,$A$6)

 

[Asbestos_Jen]

I have been struggling with a similar problem (due date/times in working hours). The math depends on start and end of shift, but I based it on 7:30-16:00 with a half hour break (at an unspecified time, so it will impact working hours less than one full day that overlap the break). I used Canadian holidays for the HolidayList, but you can use whatever you need.

2024-08-12.xlsx
	A	B	C	D	E	F	G	H	I
1		TaskStartDate	TaskEndDate		Days	FullWorkDays	End of start day	Start of End day	TotalWorkingHours
2	Task1	30-Jul-24 14:08	30-Jul-24 14:52	0:44	0	0	30-Jul-24 16:00	30-Jul-24 07:30	1:14
3	Task2	25-Jun-24 15:33	05-Jul-24 07:41	232:08	10	7	25-Jun-24 16:00	05-Jul-24 07:30	48:38
4	Task3	25-Jun-24 15:33	05-Jul-24 09:35	234:02	10	7	25-Jun-24 16:00	05-Jul-24 07:30	50:32
5	Task4	25-Jun-24 15:33	05-Jul-24 09:35	234:02	10	7	25-Jun-24 16:00	05-Jul-24 07:30	50:32
6
7
8	WorkStarts	07:30
9	WorkEnds	16:00
10	WorkHours/day	08:00
11
12	Holidays
13	1-Jan-24	Mon	New Year's Day (2024)
14	19-Feb-24	Mon	Family Day (AB,NB,ON,SK)
15	29-Mar-24	Fri	Good Friday
16	20-May-24	Mon	Victoria Day
17	1-Jul-24	Mon	Canada Day
18	5-Aug-24	Mon	Civic Holiday
19	2-Sep-24	Mon	Labour Day
20	14-Oct-24	Mon	Thanksgiving
21	31-Oct-24	Thu	Halloween
22	11-Nov-24	Mon	Remembrance Day
23	25-Dec-24	Wed	Christmas
24	26-Dec-24	Thu	Boxing Day
25	1-Jan-25	Wed	New Year's Day (2025)
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=C2-B2
E2:E5	E2	=INT(C2)-INT(B2)
F2:F5	F2	=NETWORKDAYS(S2,U2,Holidays)-1
G2:G5	G2	=INT(B2)+$B$9
H2:H5	H2	=INT(C2)+$B$8
I2:I5	I2	=G2-B2+C2-H2-$B$10+$B$10*F2
B10	B10	=B9-B8-0.5/24
B13:B25	B13	=TEXT(WEEKDAY(A13),"Ddd")

 

[Asbestos_Jen]

I just realized I posted the one with the total hours/day at 8:00 instead of 8:30. Here's both.

2024-08-12.xlsx
	A	B	C	D	E	F	G	H	I	J
1		TaskStartDate	TaskEndDate		Days	FullWorkDays	End of start day	Start of End day	TotalWorkingHours
2	Task1	30-Jul-24 14:08	30-Jul-24 14:52	0:44	0	0	30-Jul-24 16:00	30-Jul-24 07:30	1:14	0:44
3	Task2	25-Jun-24 15:33	05-Jul-24 07:41	232:08	10	7	25-Jun-24 16:00	05-Jul-24 07:30	48:38	51:38
4	Task3	25-Jun-24 15:33	05-Jul-24 09:35	234:02	10	7	25-Jun-24 16:00	05-Jul-24 07:30	50:32	53:32
5	Task4	25-Jun-24 15:33	05-Jul-24 09:35	234:02	10	7	25-Jun-24 16:00	05-Jul-24 07:30	50:32	53:32
6									8:00h day	8:30h day
7
8	WorkStarts	07:30
9	WorkEnds	16:00
10	WorkHours/day	08:00	08:30
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=C2-B2
E2:E5	E2	=INT(C2)-INT(B2)
F2:F5	F2	=NETWORKDAYS(S2,U2,Holidays)-1
G2:G5	G2	=INT(B2)+$B$9
H2:H5	H2	=INT(C2)+$B$8
I2:I5	I2	=G2-B2+C2-H2-$B$10+$B$10*F2
J2:J5	J2	=G2-B2+C2-H2-$C$10+$C$10*F2

Here's the one I was struggling with, so you can see the (very unwieldy) way I did it in the end: Calculate end date and time excluding non-work hours

 

[AhoyNC]

Another option. You will need to change cells E3 and F3 to match your start and end days for hours worked each day and
holiday column.

Book1
	A	B	C	D	E	F	G	H
1					Work Day			Holiday
2					Start	End		7/4/2024
3	Start	End	Time Worked		8:00	16:00		12/25/2024
4	7/30/2024 14:08	7/30/2024 14:52	0:44
5	6/25/2024 15:33	7/5/2024 7:41	48:27
6	6/25/2024 15:33	7/5/2024 9:35	50:02
7	8/1/2024 11:00	8/5/2024 15:02	20:02
8	8/1/2024 11:00	8/1/2024 15:00	4:00
9	12/24/2024 15:00	12/26/2024 13:00	6:00
Sheet3

Cell Formulas
Range	Formula
C4:C9	C4	=(NETWORKDAYS(A4,B4,$H$2:$H$3)-1)*($F$3-$E$3)+IF(NETWORKDAYS(B4,B4,$H$2:$H$3),MEDIAN(MOD(B4,1),$F$3,$E$3),$F$3)-MEDIAN(NETWORKDAYS(A4,A4,$H$2:$H$3)*MOD(A4,1),$F$3,$E$3)

This link explains formula.

Get work hours between dates and times

To calculate total work hours between two dates and times, you can use a formula based on the NETWORKDAYS function. In the example shown, E5 contains this formula: =(NETWORKDAYS(B5,C5)-1)*(upper-lower) +IF(NETWORKDAYS(C5,C5),MEDIAN(MOD(C5,1),upper,lower),upper)...

exceljet.net