IF AND statement for analysing a time range

[dezinsektor]

Hi, I'm trying to make a formula that will take in account two different times (hh:mm:ss) and if the value is between those times then ignore the result and write desired time called reset time.

this is my formula in cell V4 that does not work:
=IF(AND($G$4<(U4+$H$4);(U4+$H$4)<$L$4);$G$4;(U4+$H$4))

G4 H4 L4 U4 V4

reset time

waiting time

injection time

start

end

start

end

start

end

start

end

start

end

start

end

start

end

9:30:00

4:00:00

0:03:30

9:30:00​

9:33:30​

13:33:30

13:37:00

17:37:00

17:40:30

21:40:30

21:44:00

1:44:00

1:47:30

5:47:30

5:51:00

9:51:00

9:54:30

The result in V4 should be reset time in G4.

Thank you.

Cheers

 

[dezinsektor]

@dezinsektor
The problem is that you are only seeing the time of day values in the cells. A Date or Time variable is really a number with both the integer and decimal parts. The integer indicates the day and the decimal is the hour. You can see from your data below that while you are only seeing the hours of the day, the hours added together actually spill into the next day. V4 isn't just checking the hour of 9:30 against the hour of 9:51 (U + H). Instead, it is checking the Date variable as a whole.

In the table below, I copied your Time values down a row and formatted them as numbers. You see that U4 isn't just 5:47:30, but it is that time a day later. You can also see that U + H is not less than L, so your If fails.

	reset time	waiting time	injection time	start	end	start	end	start	end	start	end	start	end	start	end	start	end	start	end
buš 1	9:30:00	4:00:00	0:03:30	9:30:00	9:33:30	13:33:30	13:37:00	17:37:00	17:40:30	21:40:30	21:44:00	1:44:00	1:47:30	5:47:30	5:51:00	9:51:00	9:54:30	13:54:30	13:58:00
	0.39583	0.16667	0.00243	0.39583	0.39826	0.56493	0.56736	0.73403	0.73646	0.90313	0.90556	1.07222	1.07465	1.24132	1.24375	1.41042	1.41285	1.57951	1.58194

Try using the Evaluate Formula feature in the Formulas tab. Select V4 and run through the evaluation. You can see where the formula fails the If statement.

Thank you shknbk2, but still problem remains.
Maybe you have idea what formula to use instead this one?

 

[shknbk2]

This might work. It uses the TRUNC formula to get the decimal part of the number so that it ignores the date, which leaves just the time.

=IF(AND($G$4<(U4-TRUNC(U4)+$H$4),((U4-TRUNC(U4)+$H$4)<$L$4)),$G$4,(U4-TRUNC(U4)+$H$4))

 

[Fluff]

Another option would be to change your start formula to

=IF(AND((K4+$H$4)>=$G$4,(K4+$H$4)<=K4),$G$4,MOD(K4+$H$4,1))

so that it never goes over 1 day

 

[dezinsektor]

This might work. It uses the TRUNC formula to get the decimal part of the number so that it ignores the date, which leaves just the time.

=IF(AND($G$4<(U4-TRUNC(U4)+$H$4),((U4-TRUNC(U4)+$H$4)<$L$4)),$G$4,(U4-TRUNC(U4)+$H$4))

it works, thank you very much!
Cheers!

dezinsektor