Formula for Charged Time

[Legacy 436357]

Hello,

Does anyone know of a formula that will total up the Task Times in column I for the day of the week and put in column L?

Thank you

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
3	Task #	Date	Day of Week	AFR Number	Shop Order	Other	Start Time	End Time	Task Time		Day of Week	Charged Time
4	1	7/5/2019	Friday	86596			8:00 AM	10:00 AM	2:00		Monday
5	2				W203:001		10:00 AM				Tuesday
6	3										Wednesday
7	4										Thursday
8	5										Friday
Week 27

 

[Aryatect]

As Column I contains text array formula can be used

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	#	Date	Day of Week	AFR Number	Shop Order	Other	Start Time	End Time	Task Time		Day of Week	Charged Time
2	1	7/5/2019	Friday	86596			8:00 AM	10:00 AM	2:00		Monday	0:00
3	2				W203:001		10:00 AM				Tuesday	0:00
4	3										Wednesday	0:00
5	4										Thursday	0:00
6	5										Friday	2:00
Sheet2

Cell Formulas
Range	Formula
L2	{=SUM(IF($C$2:$C$6=$K2,$I$2:$I$6*1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Easier would be Change Column I to =IF('Week 27'!$H8<>"",H8-G8,"") and change the number format to h:mm and use SUMIF (=SUMIF($C$2:$C$6,$K2,$I$2:$I$6))

 

[Legacy 436357]

Thank you I adjusted the ranges on the array formula.

For Friday the task time so far is 2 hours. The formula yields 0.08 why?

I forgot to format to h:mm

Thanks for the help

 

[Legacy 436357]

I noticed if I start a new task and have the date entered but no End Time the formula yields #VALUE ! error.

 

[Aryatect]

See if this works:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	#	Date	Day of Week	AFR Number	Shop Order	Other	Start Time	End Time	Task Time		Day of Week	Charged Time
2	1	7/5/2019	Friday	86596			8:00 AM	10:00 AM	2:00		Monday	0:00
3	2	7/4/2019	Thursday		W203:001		10:00 AM				Tuesday	0:00
4	3	7/4/2019	Thursday				10:00 AM	12:00 PM	2:00		Wednesday	0:00
5	4										Thursday	2:00
6	5										Friday	2:00
Sheet2

Cell Formulas
Range	Formula
L2	{=SUM(IF(($H$2:$H$6<>"")*($C$2:$C$6=$K2),$I$2:$I$6*1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Legacy 436357]

Thank you after adjusting the ranges again it works great!

 

[Aryatect]

Glad could help!