Complex If then?

[bpc]

I have a start time(22:13) in b3 and an end time(3:06) in c3. All times are
entered in 24:00 format. In column F, I have one hour increment time values
(00:00 in f3, 01:00 in f4, 02:00 in f5, etc. In column G next to the
appropriate hour I would like to place an X if the event occurred during that
time hour. So in the example above (22:13 - 3:06) X's would be placed for
22:00, 23:00, 00:00, 01:00, 02:00, 03:00. Suggestions? The two formulas I
create both work but not 100%. The first formula works when the time range
goes over the midnight hour (22:13 - 06:12)but does not work on time ranges
over a one day period (2:04 - 6:03). The second formula works on same day
ranges but not ranges crossing 24:00.
Help, I need one formula to do both.

=IF(OR(AND($B$3>=F3,$B$3<F4),AND($C$3>=F3,$C$3<F4),OR(F3>=$B$3,F3<=$C$3)),"x","")

=IF(OR(AND($B$3>=F3,$B$3<F4),AND($C$3>=F3,$C$3<F4),OR(AND(F3>=$B$3,F3<=$C$3))),"x","")

 

[Sean Timmons]

=IF($B$3>$C$3,IF(AND($B$3<F$3,$C$3>F$3),"X",""),IF(OR($C$3>F$3,F$3>$B$3),"X",""))

Should get it

 

[bpc]

Getting there. I couldn't get your formula to work but I changed the first >
to a < and it almost works. The exception is that it does not acurrately "X"
the first hour of the series before noon (12:00) and the last hour in the
series after noon is not "X" properly.

 

[Sean Timmons]

so, you mean midnight? You may want to use 12:01 AM and 11:59 PM to avoid
this issue...

 

[T. Valko]

Try this:

=IF(B$3<C$3,IF(OR(AND(B$3<F3,C$3>F3),HOUR(B$3)=HOUR(F3)),"X",""),IF(OR(C$3>F3,HOUR(B$3)=HOUR(F3),F3>B$3),"X",""))

Assumes you want to mark the hour when the start time = the hour but do not
mark the hour when the end time = the hour. For example:

7:00 to 13:00

7:00 will be marked but 13:00 will not.

Note that if start = end every cell will be marked.

7:00 to 7:00

Which the formula evaluates as essentially a whole day.