Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I write an IF/other statement to cut off time data? | Excel Worksheet Functions | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
Data Connection wizard quits working | Excel Discussion (Misc queries) |