time schedule

[william marinho]

Good morning, I must make a time schedule that tells me how many hours in the night shift i have worked, night shift is between 22:00 and 05:00 in the morning of the next day.
I am using this formula =IF(H17>=V$15;MOD(W$15-H17;1);IF(H17<=W$15;MOD(W$15-H17;1);(IF(K17>=V$15;MOD(K17-V$15;1);IF(K17<=V$15;MOD(K17-V$15;1);0))))) but if I work in a time different of 22:00 to 05:00 lets say 13:00 to 19:00 the cell shows me 21:00 when it should show 00:00. How can I fix it?

 

[Bosquedeguate]

Please provide a minisheet of the cells you are referencing. Include labels or a description of the contents of each cell referenced in your formula.

 

[william marinho]

Here is an example, first column is the the predicted time schedule, no problems with that, then the real time worked. You can see, if I work out of the range between 22:00 and 05:00 the I will have 21:00 marked in the cell where 00:00 must be shown. If I finish my work at 22:00 then it start the counting correctly. I left it highlighted. I hope you guys can help me. What I am trying to do is, if I work my shift out of the range 22:00 to 05:00 of the next day I have 00:00 as response, and if I work within this range it must show me the of hours that I worked on this range i.e. if I start to work at 21:00 and finish at 06:00 of the next day this column must show me 07:00, if I start ate 00:00 and work until 06:00 it must show me 05:00.
Thank you

 

[Bosquedeguate]

What are the values in
V$15 and W$15?

 

[william marinho]

What are the values in
V$15 and W$15?

V$15 = 22:00
W$15= 05:00

 

[Bosquedeguate]

Will this provide the results you need?

Book1
	A	B	C	D	E	F	G
1	start time	end time	night shift hours	Night Shift Start	22:00	Night Shift End	5:00
2	13:00	22:00	0:00
3	22:00	4:00	6:00
4	13:00	19:00	0:00
5	22:00	23:00	1:00
6	22:00	23:59	1:59
7	22:00	1:00	3:00
8	22:00	2:00	4:00
9	22:00	3:00	5:00
10	22:00	4:00	6:00
11
Sheet2

Cell Formulas
Range	Formula
C2:C10	C2	=IF(AND(B2>A2,B2<=NSCom),0,IF(B2>=A2,B2-A2,TIMEVALUE("23:59:59")-(A2-0.00001)+(B2-TIMEVALUE("00:00:00"))))

Named Ranges
Name	Refers To	Cells
NSCom	=Sheet2!$E$1	C2:C10

 

[william marinho]

Will this provide the results you need?

Book1
	A	B	C	D	E	F	G
1	start time	end time	night shift hours	Night Shift Start	22:00	Night Shift End	5:00
2	13:00	22:00	0:00
3	22:00	4:00	6:00
4	13:00	19:00	0:00
5	22:00	23:00	1:00
6	22:00	23:59	1:59
7	22:00	1:00	3:00
8	22:00	2:00	4:00
9	22:00	3:00	5:00
10	22:00	4:00	6:00
11
Sheet2

Cell Formulas
Range	Formula
C2:C10	C2	=IF(AND(B2>A2,B2<=NSCom),0,IF(B2>=A2,B2-A2,TIMEVALUE("23:59:59")-(A2-0.00001)+(B2-TIMEVALUE("00:00:00"))))

Named Ranges
Name	Refers To	Cells
NSCom	=Sheet2!$E$1	C2:C10

 

[william marinho]

my excel doesnt recognize the function NSCom

 

[william marinho]

I had to do an adjustment but now is working on the opposite way

=SE(E(K18>H18;0;SE(K18>=H18;K18-H18;VALOR.TEMPO("23:59:59")-(H18-VALOR.TEMPO("00:00:01")+(K18-VALOR.TEMPO("00:00:00")))))

 

[Bosquedeguate]

my excel doesnt recognize the function NSCom

I named the Ranges V15 and W15, NSCom Night Shift Comienza (NSCom) and NSFin (Night Shift Fin)