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

 

[shknbk2]

I'm not sure what your problem is. Other than changing the semicolons in your formula to commas, I seem to get the G4 value.

(By the way, your data above would have been easier to understand if you would have used the XL2BB table export feature like below)

Book1
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
3	reset time	waiting time	injection time	start	end	start	end	start	end	start	end	start	end	start	end	start	end
4	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:30:00	9:54:30
Sheet1

Cell Formulas
Range	Formula
V4	V4	=IF(AND($G$4<(U4+$H$4),(U4+$H$4)<$L$4),$G$4,(U4+$H$4))

 

[dezinsektor]

thank you, but semicolon is not an issue, this is regional and language setting. It is interesting that works fine on your computer.
?

 

[Fluff]

Check that all your values are real times & not text.

 

[dezinsektor]

Check that all your values are real times & not text.

yes, I did, everything is in time format hh:mm:ss

 

[dezinsektor]

Here is my Excel:

Reset_if_and.xlsx

drive.google.com

 

[Fluff]

Which of those cells do you think is wrong?

 

[dezinsektor]

Which of those cells do you think is wrong?

V4, it should be 9:30, not 9:51.

 

[Fluff]

Why? U4:H4 is greater than L4

 

[shknbk2]

@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.