I work in a call center and I am making a spreadsheet to see how many agents
we have on the phone by there schedules. (in 15 min. increments) It looks
like this:

A B C D E F G
H I J ......
3 NAME START STOP LUNCH 800 815 830 845 900
.......
4 JOHN 815 1715 1200 1300 X X X X
X ......

I made an If function to mark an X if the employee is there by their start
and stop time but I can't get it to not put an X when there are on lunch. The
formula that I have now is: =IF(AND($F3=$B4,$F3<$C4),"X","")

The ones that I have tried but dont work are
: =IF(AND(F$3=$B4,F$3<$C4),IF(AND(F$3<$D4,F$3<$E4), "X",""))
and: =IF(AND(F$3=$B4,F$3<$C4),IF(F$3<$D4,"X",IF(F$3<$E 4,"X")),"")

Please Help!! Thanks in advance.
~ROOKIE~

try this
http://www.microsoft.com/office/comm...a ng=en&cr=US
--
paul
remove nospam for email addy!



"Rookie" wrote:

I work in a call center and I am making a spreadsheet to see how many agents
we have on the phone by there schedules. (in 15 min. increments) It looks
like this:

A B C D E F G
H I J ......
3 NAME START STOP LUNCH 800 815 830 845 900
......
4 JOHN 815 1715 1200 1300 X X X X
X ......

I made an If function to mark an X if the employee is there by their start
and stop time but I can't get it to not put an X when there are on lunch. The
formula that I have now is: =IF(AND($F3=$B4,$F3<$C4),"X","")

The ones that I have tried but dont work are
: =IF(AND(F$3=$B4,F$3<$C4),IF(AND(F$3<$D4,F$3<$E4), "X",""))
and: =IF(AND(F$3=$B4,F$3<$C4),IF(F$3<$D4,"X",IF(F$3<$E 4,"X")),"")

Please Help!! Thanks in advance.
~ROOKIE~

Try using OR....

=IF(OR(AND(F$3=$B4,F$3<$D4),AND(F$3$E4,F$3<$C4)) ,"X","")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=531102

=IF(OR($B4F$3,$C4<=F$3),"h",IF(AND($D4<=F$3,$E4F $3),"l","x"))

remove the h and l when you have tested this (ie, make "")

--

Rookie Wrote:
I work in a call center and I am making a spreadsheet to see how many
agents
we have on the phone by there schedules. (in 15 min. increments) It
looks
like this:

A B C D E F G
H I J ......
3 NAME START STOP LUNCH 800 815 830 845
900
.......
4 JOHN 815 1715 1200 1300 X X X
X
X ......

I made an If function to mark an X if the employee is there by their
start
and stop time but I can't get it to not put an X when there are on
lunch. The
formula that I have now is: =IF(AND($F3=$B4,$F3<$C4),"X","")

The ones that I have tried but dont work are
: =IF(AND(F$3=$B4,F$3<$C4),IF(AND(F$3<$D4,F$3<$E4), "X",""))
and: =IF(AND(F$3=$B4,F$3<$C4),IF(F$3<$D4,"X",IF(F$3<$E 4,"X")),"")

Please Help!! Thanks in advance.
~ROOKIE~


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=531102