time duration in one cell

[kelvin_9]

hi all, may i know is it possible to count the time duration between start & end and deduct 45min for the meal break in one cell?

09:15 - 18:45	8.75
09:15 - 18:45	8.75
10:45 - 21:00	9.5
13:00 - 21:00	7.25

thanks for the help

 

[FormR]

Hi, here is one option.

Book1
	A	B
1	09:15 - 18:45	8.75
2	09:15 - 18:45	8.75
3	10:45 - 21:00	9.5
4	13:00 - 21:00	7.25
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=(RIGHT(A1,5)-"00:45"-LEFT(A1,5))*24

 

[kelvin_9]

thanks for your option!
i'm sorry that i wonder can i sum it together(total 34.25) at once or i need to use your formula, then sum ?

 

[FormR]

or i need to use your formula, then sum

That's probably the easier, more transparent way, but it can be done in one step.

Book1
	A
1	09:15 - 18:45
2	09:15 - 18:45
3	10:45 - 21:00
4	13:00 - 21:00
5
6	34.25
Sheet1

Cell Formulas
Range	Formula
A6	A6	=(SUMPRODUCT(RIGHT("00:00"&A1:A4,5)-LEFT(A1:A4&"00:00",5))-("00:45"*COUNTA(A1:A4)))*24

 

[kelvin_9]

Book1
	C
51	10:00 - 19:30
52
53
54	09:15 - 18:45
55	09:15 - 18:45
56	11:30 - 21:00
57
58	11:30 - 21:00
59	00:00 - 00:00
60	09:15 - 18:45
61
62
63
64	09:15 - 18:45
65	09:15 - 18:45
66	11:30 - 21:00
67	09:15 - 18:45
68
69	11:30 - 21:00
70
71	11:30 - 21:00
72	11:30 - 21:00
73	09:15 - 18:45
74
75	09:15 - 18:45
76	00:00 - 00:00
77
78	09:15 - 18:45
79	09:15 - 18:45
80	11:30 - 21:00
81	11:30 - 21:00
82	09:15 - 18:45
83	00:00 - 00:00
84	165.25
RESULT-1

Cell Formulas
Range	Formula
C84	C84	=(SUMPRODUCT(RIGHT("00:00"&C51:C83,5)-LEFT(C51:C83&"00:00",5))-("00:45"*COUNTA(C51:C83)))*24

Named Ranges
Name	Refers To	Cells
'RESULT-1'!Print_Area	='RESULT-1'!$A$39:$AD$85	C84

thanks for your prompt reply
what's wrong with mine on those 00:00 - 00:00?
it should be 166.25?

 

[FormR]

it should be 166.25

I think it should be 175 - to handle the zero times (and blanks produced by a formula) try:

=(SUMPRODUCT(RIGHT("00:00"&C51:C83,5)-LEFT(C51:C83&"00:00",5))-("00:45"*COUNTIFS(C51:C83,"?*",C51:C83,"<>00:00 - 00:00")))*24

 

[kelvin_9]

ugh despite i dont know why it is 175, but if i use your first formula which in #2 for every one and sum total, it is exactly 175 exclude 00:00 - 00:00

THANK YOU SO MUCH FOR YOUR HELP.

 

[kelvin_9]

Hi FormR
can this formula be used in this case when my employee applied leave like this table?
thank you very much

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		17.5		26.25		26.25		#VALUE!		#VALUE!		#VALUE!		17.5
2
3	Bee	09:30 - 19:00						11:30 - 21:00		11:30 - 21:00		12:00 - 21:30		12:00 - 21:30			43.75
4	Brenda	00:00 - 00:00		09:15 - 18:45		09:15 - 18:45		Unpaid Leave Day 1.0		Unpaid Leave Day 1.0		Unpaid Leave Day 1.0					#VALUE!
5	Brian			09:15 - 18:45		09:15 - 18:45		10:00 - 19:30		11:30 - 21:00				10:00 - 19:30			43.75
6	Carlie	09:15 - 18:45		09:15 - 18:45		12:00 - 21:30				Vacation Leave Day 1.0		Vacation Leave Day 1.0					#VALUE!
Sheet1

Cell Formulas
Range	Formula
B1, N1, L1, J1, H1, F1, D1	B1	=(SUMPRODUCT(RIGHT("00:00"&B3:B6,5)-LEFT(B3:B6&"00:00",5))-("00:45"*COUNTIFS(B3:B6,"?*",B3:B6,"<>00:00 - 00:00")))*24
Q3:Q6	Q3	=(SUMPRODUCT(RIGHT("00:00"&B3:O3,5)-LEFT(B3:O3&"00:00",5))-("00:45"*COUNTIFS(B3:O3,"?*",B3:O3,"<>00:00 - 00:00")))*24