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]

[TABLE="width: 520"]
<colgroup><col><col><col><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD]Task List
[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Duration[/TD]
[TD]Result[/TD]
[TD="align: right"]0:30[/TD]
[TD="align: right"] 0:45[/TD]
[TD="align: right"] 1:00[/TD]
[TD="align: right"] 1:15[/TD]
[TD="align: right"] 1:30[/TD]
[TD="align: right"] 1:45[/TD]
[/TR]
[TR]
[TD]Pre Arrival[/TD]
[TD]6:10[/TD]
[TD]7:10[/TD]
[TD]1:00[/TD]
[TD]Comp1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Arrival[/TD]
[TD]7:10[/TD]
[TD]8:40[/TD]
[TD]1:30[/TD]
[TD]Comp1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Pre Departure[/TD]
[TD]22:10[/TD]
[TD]23:10[/TD]
[TD]1:00[/TD]
[TD]Comp2 [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Check-in[/TD]
[TD]23:10[/TD]
[TD]1:10[/TD]
[TD]2:00[/TD]
[TD]Comp3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Boarding[/TD]
[TD]1:10[/TD]
[TD]2:10[/TD]
[TD]1:00[/TD]
[TD]Comp4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Post Departure[/TD]
[TD]2:10[/TD]
[TD]3:10[/TD]
[TD]1:00[/TD]
[TD]Comp4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

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