IF function
 

I'm trying to use the If function for a schedule.

If cell F5 = 11a-7p, then 7.5, if F5=6a-1:30p, then 7,If 6:30a-1:30p, then
6.5.

I keep getting the INVALID error.

IF function
 

On Tue, 4 Dec 2007 18:30:01 -0800, Sunshine wrote:

I'm trying to use the If function for a schedule.

If cell F5 = 11a-7p, then 7.5, if F5=6a-1:30p, then 7,If 6:30a-1:30p, then
6.5.

I keep getting the INVALID error.

First, you will have to give some different order to your overlapping
conditions.
If they are in order you wrote, then you will never get 6.5 as a result
(because the middle condition includes all of the later).
Same thing with time between 11am and 13:30pm - first condition takes it
first.

Besides that, the correct formula would be (in one row, of course):
=IF(AND(F5=TIME(11,0,0),F5<=TIME(19,0,0)),7.5,IF( AND(F5=TIME(6,0,0),F5<=TIME(13,30,0)),7,IF(AND(F5 =TIME(6,30,0),F5<=TIME(13,30,0)),6.5,0)))

If you look at it differently, this is the logic of it:

=IF(AND(F5=TIME(11,0,0),F5<=TIME(19,0,0)),
7.5,
IF(AND(F5=TIME(6,0,0),F5<=TIME(13,30,0)),
7,
IF(AND(F5=TIME(6,30,0),F5<=TIME(13,30,0)),
6.5,
0)
)
)

hth
B.