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


cant seem to get this to work for post midnight times when it should be adding a day tomake the time greater than previous day time

 

[Chrisdontm]

Try this;

=IF(B57="","",IF(F53="","",IF(MOD(F53-B57,1)*24=0,"",MOD(F53-B57,1)*24)))

 

[mole999]

Or

=IF(B57< E53,B57-E53,1-(B57-E53))<e53,b57-e53,1-(b57-e53))< html=""></e53,b57-e53,1-(b57-e53))<>

 

[neilmark]

Try this;

=IF(B57="","",IF(F53="","",IF(MOD(F53-B57,1)*24=0,"",MOD(F53-B57,1)*24)))

this does not seem to work ,

So the formula is in cell F57 , if the statement is true then it should bring up the result from cell E57.
The time field in Row 53 . I am trying to compute if 0:30 is greater than 23:10(B57) then the result should be Comp3 (E57)
0:30 should ideally be greater than 23:10 because it is the next day

[TABLE="width: 541"]
<colgroup><col><col><col><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD]Task List 1
(Flight 1)[/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]
[TD="align: right"] 2:00[/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]
[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]
[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]
[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]
[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]
[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]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[neilmark]

this does not seem to work ,

So the formula is in cell F57 , if the statement is true then it should bring up the result from cell E57.
The time field in Row 53 . I am trying to compute if 0:30 is greater than 23:10(B57) then the result should be Comp3 (E57)
0:30 should ideally be greater than 23:10 because it is the next day

[TABLE="width: 541"]
<tbody>[TR]
[TD]Task List 1
(Flight 1)[/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]
[TD="align: right"] 2:00[/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]
[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]
[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]
[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]
[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]
[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]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

if we use the formula IF(MOD(F53-B57,1) then if B57(0:30 ) changes to 01:10 it will assume 0:30 is still greater as it adds another day