IF Function Excel ( Time value changes )

[shiraz50]

Hi .
I need to do a if functions that will show the following .I added in some inputs manually. Please help.

If column N is between time range of 4:00 to 6:07 minutes then add column N time in Q
If column N is between time range of 6:08 to 7:34 minutes then add column N time in R
If column N is > 7:34 minutes then add column N time in S

 

[etaf]

=IF(N2>TIMEVALUE("00:07:34"),N2+S2,IF(N2>TIMEVALUE("00:06:07"),N2+R2,IF(N2>TIMEVALUE("00:04:00"),N2+Q2,"")))

Book2
	N	O	P	Q	R	S	T
1
2	00:03:00			1:00	2:00	3:00
3	00:03:30			1:00	2:00	3:00
4	00:04:00			1:00	2:00	3:00
5	00:04:30			1:00	2:00	3:00	01:04:30
6	00:05:00			1:00	2:00	3:00	01:05:00
7	00:05:30			1:00	2:00	3:00	01:05:30
8	00:06:00			1:00	2:00	3:00	01:06:00
9	00:06:30			1:00	2:00	3:00	02:06:30
10	00:07:00			1:00	2:00	3:00	02:07:00
11	00:07:30			1:00	2:00	3:00	02:07:30
12	00:08:00			1:00	2:00	3:00	03:08:00
Sheet1

Cell Formulas
Range	Formula
T2	T2	=IF(N2>TIMEVALUE("00:07:34"),N2+S2,IF(N2>TIMEVALUE("00:06:07"),N2+R2,IF(N2>TIMEVALUE("00:04:00"),N2+Q2,"")))
T3:T12	T3	=IF(N3>TIMEVALUE("0:7:34"),N3+S3,IF(N3>TIMEVALUE("0:6:07"),N3+R3,IF(N3>TIMEVALUE("0:4:00"),N3+Q3,"")))

 

[kevin9999]

If you can put those time parameters somewhere on your sheet (I put them in row 4 - shift them to suit) then does this work for you?

Book1
	N	O	P	Q	R	S
4				04:00	06:07	07:34
5	04:39			04:39
6	07:06				07:06
7	06:31				06:31
8	07:35					07:35
9	07:20				07:20
Sheet1

Cell Formulas
Range	Formula
Q5:Q9	Q5	=IF(AND($N5>=Q$4,$N5<=R$4),$N5,"")
R5:R9	R5	=IF(AND($N5>R$4,$N5<=S$4),$N5,"")
S5:S9	S5	=IF($N5>S$4,$N5,"")

 

[SunnyAlv]

Like This ?


Range Q1

=IF(AND(TEXT(N1,"hh:mm")>TEXT("04:00","hh:mm"),TEXT(N1,"hh:mm")<TEXT("06:07","hh:mm")),TEXT(N1,"hh:mm"),"")

Range R1

=IF(AND(TEXT(N1,"hh:mm")>TEXT("06:08","hh:mm"),TEXT(N1,"hh:mm")<TEXT("07:34","hh:mm")),TEXT(N1,"hh:mm"),"")

Range S1

=IF(TEXT(N1,"hh:mm")>TEXT("07:34","hh:mm"),TEXT(N1,"hh:mm"),"")

*I hope this helps your problem, If there’s any unclear information please let me know

 

[shiraz50]

Like This ?


Range Q1

=IF(AND(TEXT(N1,"hh:mm")>TEXT("04:00","hh:mm"),TEXT(N1,"hh:mm")<TEXT("06:07","hh:mm")),TEXT(N1,"hh:mm"),"")

Range R1

=IF(AND(TEXT(N1,"hh:mm")>TEXT("06:08","hh:mm"),TEXT(N1,"hh:mm")<TEXT("07:34","hh:mm")),TEXT(N1,"hh:mm"),"")

Range S1

=IF(TEXT(N1,"hh:mm")>TEXT("07:34","hh:mm"),TEXT(N1,"hh:mm"),"")

View attachment 103371

*I hope this helps your problem, If there’s any unclear information please let me know

Thanks for the prompt response . U guys are legends

 

[kevin9999]

Glad we could help & thanks for the feedback