Time Function help with formula needed

[neilmark]

I need help with a formula in Row 53 column F I have time at 0:30
In Row 57 column B I have time at 23:10
in Row 57 column E I have result which is comp3

the formula I am using works for all times from 01:00 to 23:00 but post midnight it doesn't work

this is what I am using in cell 57 (F)
=IFERROR(IF(AND(F$53>=$B57,F$53<$C57),$E57," ")," ")

0:30 should show as greater than 23:10 because its the next day

any help would be appreciated

 

[steve the fish]

Hi. What is the purpose of this section:

AND(F$53>=$B57,F$53<$C57)

 

[neilmark]

Hi. What is the purpose of this section:

AND(F$53>=$B57,F$53<$C57)

Hi Steve

in F$53 I have the time 0:30 and in B$57 I have another time 23:10 , and in C57 I have time 01:10

technically midnight 30(0:30 is greater than 23:10 ) so If this is true I want to show the result which is in E57

excel is not seeing that 0:30 Is the next day and 23:10 is the previous day

 

[steve the fish]

Sure i get that bit. Im just not sure why you are testing F53>=B57? What is the purpose of this test?

 

[neilmark]

Sure i get that bit. Im just not sure why you are testing F53>=B57? What is the purpose of this test?

Hi Steve

I'm testing this because the times in B57 and C57 keep changing depending on a dashboard selection

 

[neilmark]

so basically if F$53 is greater than or Equal to B57 and if F$53 is less than B57 then the result is E57 if not the cell F57 should be blank

 

[neilmark]

reposting - so basically if F$53 is greater than or Equal to B57 and if F$53 is less than C57 then the result is E57 if not the cell F57 should be blank

 

[neilmark]

Task List	Start	End	Duration	Result	0:30	0:45	1:00	1:15	1:30	1:45
Pre Arrival	6:10	7:10	1:00	Comp1
Arrival	7:10	8:40	1:30	Comp1
Pre Departure	22:10	23:10	1:00	Comp2
Check-in	23:10	1:10	2:00	Comp3
Boarding	1:10	2:10	1:00	Comp4
Post Departure	2:10	3:10	1:00	Comp4

<colgroup><col><col><col><col><col><col><col span="5"></colgroup><tbody>
</tbody>

 

[steve the fish]

Im sorry i just dont know what you are asking. What is this table? Under 0:30 is something supposed to show?

 

[neilmark]

Im sorry i just dont know what you are asking. What is this table? Under 0:30 is something supposed to show?

hi Steve

I don't have access to upload an attachment

so assume row 53 is the time fileds which starts at F53 from 00:30-23:45
the formula kicks in so if the criterea is not met the cell will be black if it is met then the cell gets highlighted through conditional formatting .