IF statement for time data not working properly

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Daren - 07 Nov 2006 20:30 GMT

I have time data that captures start and end times. For these distinct
times, there is an if statement that evaluates whether these times occurred
in a certain time range. For example, if start time was 8:00 am and end time
10:00 am, the IF statement would evaluate whether those times occured between
7:00 am and 12:00 pm.
The formula is:
=IF(AE33>0,IF(AF33=0,1," ")," "), where the value in column AE is some time
greater than 0, and AF is some time equal to 0. That essentially means AF
would be blank. So if the time was indeed 8:00 am - 10:00 am, and the range
I want to capture in is 7:00 am - 12:00 pm, then this formula should be
entering a 1, but it isn't.

Therefore, what might be the cause of the formula not making the computation?

Thanks.

Reply to this Message

Biff - 07 Nov 2006 21:42 GMT

> =IF(AE33>0,IF(AF33=0,1," ")," ")

If AE33 = 8:00 AM and AF33 = 10:00 AM but you're formula is testing AF33=0
so it has to evaluate as FALSE then returns a space.

Maybe you want something like this:

=IF(AND(AE33>0,AF33>0),1,"")

If you want to test for the times being between (inclusive or exclusive?) a
set span:

AE33 = 8:00 AM
AF33 = 10:00 AM

Test span:

7:00 AM
12:00 PM

=IF(AND(AE33>TIME(7,0,0),AF33<TIME(12,0,0)),1,"")

Or:

=IF(AND(AE33>=TIME(7,0,0),AF33<=TIME(12,0,0)),1,"")

Better to use cells to hold the time span criteria:

AE32 = 7:00 AM
AF32 = 10:00 PM

=IF(AND(AE33>AE32,AF33<AF32),1,"")

Biff

>I have time data that captures start and end times. For these distinct
> times, there is an if statement that evaluates whether these times
[quoted text clipped - 17 lines]
>
> Thanks.

Reply to this Message

Daren - 07 Nov 2006 23:10 GMT

Should've mentioned that my formula worked before in other Excel
spreadsheets, so I'm not sure why it's not working in this spreadsheet.

> > =IF(AE33>0,IF(AF33=0,1," ")," ")
>
[quoted text clipped - 52 lines]
> >
> > Thanks.

Reply to this Message