Complex If then?
 

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","")

Complex If then?
 

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

Should get it

"bpc" wrote:

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","")

Complex If then?
 

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" wrote:

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

Should get it

"bpc" wrote:

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","")

Complex If then?
 

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

"bpc" wrote:

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" wrote:

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

Should get it

"bpc" wrote:

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","")

Complex If then?
 

Try this:

=IF(B$3<C$3,IF(OR(AND(B$3<F3,C$3F3),HOUR(B$3)=HOU R(F3)),"X",""),IF(OR(C$3F3,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.

--
Biff
Microsoft Excel MVP


"Sean Timmons" wrote in message
...
so, you mean midnight? You may want to use 12:01 AM and 11:59 PM to avoid
this issue...

"bpc" wrote:

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" wrote:

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

Should get it

"bpc" wrote:

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","")