IF statement for time data not working properly
 

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(AE330,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.

IF statement for time data not working properly
 

=IF(AE330,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(AE330,AF330),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(AE33TIME(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(AE33AE32,AF33<AF32),1,"")

Biff

"Daren" wrote in message
...
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(AE330,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.

IF statement for time data not working properly
 

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.

"Biff" wrote:

=IF(AE330,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(AE330,AF330),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(AE33TIME(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(AE33AE32,AF33<AF32),1,"")

Biff

"Daren" wrote in message
...
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(AE330,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.