Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 162
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default 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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 162
Default 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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I write an IF/other statement to cut off time data? Daren Excel Worksheet Functions 15 November 3rd 06 12:49 AM
Inputting data to one worksheet for it effect another daedalus1 Excel Discussion (Misc queries) 1 June 25th 06 04:39 PM
From several workbooks onto one excel worksheet steve Excel Discussion (Misc queries) 6 December 1st 05 09:03 AM
Printing data validation scenarios SJC Excel Worksheet Functions 14 July 24th 05 12:43 AM
Data Connection wizard quits working SJR@mbci Excel Discussion (Misc queries) 0 June 22nd 05 03:59 PM


All times are GMT +1. The time now is 10:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"