If statement

[Guest]

Please can someone help me on this, I have been struggling with this for
hours and can't find a solution. I want to display a 1 if the time is before
13:00 and a 2 if the time is between 13:00 and 17:00 and a 3 if the time is
after 17:00 but I cannot get the if statement to give me the correct result
between 13:00 and 17:00. I have tried this but it won't work
=IF((D53*24)<=13,"1",IF(D53*24>=13<=17,"2",IF(D53*24>=17,"3")))

a
10:00:28
10:01:30
10:02:47
13:00:02
13:12:57
13:19:25
17:06:52
17:11:27
17:14:27

 

[Bob Phillips]

=IF(D53<--"13:00:00",1,IF(D53<=--"17:00:00",2,3))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Sorry Bob, this doesn't work, unfortunatley this just puts a 3 in every cell
including the ones before 13:00. BTW what does the -- indicate?

 

[David Biddulph]

Your syntax error is at
IF(D53*24>=13<=17,...
where, if you needed that condition, it would need to be
IF(AND(D53*24>=13,D53*24<=17),...
but you don't need >=13 when you've already trapped <=13 in the previous
test.

Try =IF((D53*24)<=13,"1",IF(D53*24<=17,"2","3"))

Also bear in mind that your answers "1", "2", "3" are text. if you want
them as numbers, remove the double quotes.

 

[Guest]

Excellent this works now, thanks for your help.

Your syntax error is at
IF(D53*24>=13<=17,...
where, if you needed that condition, it would need to be
IF(AND(D53*24>=13,D53*24<=17),...
but you don't need >=13 when you've already trapped <=13 in the previous
test.

Try =IF((D53*24)<=13,"1",IF(D53*24<=17,"2","3"))

Also bear in mind that your answers "1", "2", "3" are text. if you want
them as numbers, remove the double quotes.

 

[Bob Phillips]

It doesn't, it puts 1, 2 or 3 depending upon the TIME value in D53.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)