nested if excel with or and dates

September 28, 2011 at 20:19:31
Specs: Windows 7
This should be simple and I thought I had it nailed, but the formula is not working, so I am obviously doing something wrong.

I have two adjacent columns. The first column is always populated with a date and the second column may be populated with a date or may be blank. I am trying to determine if one or both dates are greater than 3/1/2008, then the row is "OK"; if neither date (or if the second column is blank) is before 3/1/2008, then that row is out.

Here's what I tried to use: =IF(OR(H2>DATEVALUE("03/01/2008"),I2>DATEVALUE("03/01/2008")),"OK""OUT").

The formula just does not return the correct result. Thanks for the help.


See More: nested if excel with or and dates

Report •


#1
September 29, 2011 at 01:33:52

Report •

#2
September 29, 2011 at 05:23:51
Thanks, I do have that in my excel formula -- just missed it in the post.

Report •

#3
September 29, 2011 at 05:44:25
Are you sure all your Dates in column H & I are all real dates?
Not just text strings that look like dates?

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
September 29, 2011 at 06:03:03
re: "I do have that in my excel formula -- just missed it in the post."

You do know that you can Copy/Paste from an Excel spreadsheet into a post, right? No need to retype the formula and chance causing confusion if you type it wrong, as appears to be the case here.

Your formula (with the comma) works fine for me. I'd check on what Mike asked about.

Excel often treats "dates" as text strings if they have been downloaded from a website or imported from certain databases. Unfortunately, the treatment is not consistent, nor is the method used to convert the Text strings into Dates.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
September 29, 2011 at 06:17:05
Good thought as I am not certain since the data was imported from another program. However, a simple IF statement does work if I just use one column.

Report •

#6
September 29, 2011 at 08:13:41
One way to check would be to try the DATEVALUE function.

If the value in H2 (or I2) that looks like a date is really a Text String, DATEVALUE(H2) (or I2) should return a 5 digit number representing the Date which can then be formatted as a date.

If DATEVALUE returns a #VALUE error, then it really is a date.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
September 29, 2011 at 18:20:53
DATEVALUE function returned a #VALUE error, so I guess it is really a date. Since it worked for some, there must be an error somewhere. At least I had the formula correct. I appreciate all the input thus far, but if anyone else has any ideas, they would be creatly appreciated as I still am at a loss.

Report •

#8
September 29, 2011 at 19:34:50
Are you doing columns of data or only the two cells H2 & I2
Post an example of your data, that may offer some clues.

MIKE

http://www.skeptic.com/


Report •

#9
September 29, 2011 at 20:03:02
HIRE DATE REHIRE DATE
11/30/1990 04/25/2001
04/11/1992 05/01/1996
06/26/1991 06/05/2008
03/12/1999
08/05/1998 09/24/2008
11/16/1995 02/06/2008
02/26/1999 03/22/2002
06/26/1998
07/27/1988
05/08/1991
05/25/1998 06/11/2008
10/25/1991 01/11/2006
05/15/1999
03/22/1996 08/29/2005
09/30/1993 10/15/2008

So, if the individual was hired or rehired prior to 3/1/2008, they are "OUT". However, if either (thus the OR statement) the hire or rehire date is after 3/1/2008, they are "OK". Rows 1 & 2 would be out, but three should be in since that individual was rehired after 3/1/2008.


Report •

#10
September 29, 2011 at 22:24:08
You haven't told us what problem you are having.

Here's what your formula gave me. Is this correct or not?

HIRE DATE	REHIRE DATE	
11/30/90	04/25/01	OUT
04/11/92	05/01/96	OUT
06/26/91	06/05/08	OK
03/12/99		        OUT
08/05/98	09/24/08	OK
11/16/95	02/06/08	OUT
02/26/99	03/22/02	OUT
06/26/98		        OUT
07/27/88		        OUT
05/08/91		        OUT
05/25/98	06/11/08	OK
10/25/91	01/11/06	OUT
05/15/99		        OUT
03/22/96	08/29/05	OUT
09/30/93	10/15/08	OK

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
September 30, 2011 at 05:13:51
Your example is correct. I, however, get "OUT" for every row.

Report •

#12
September 30, 2011 at 05:22:05
Since your formula returns the correct values for DerbyDad03,
I would still think it's your data.

Try this:

Select the entire Hire/Rehire columns
Select Format
Select Numbers

ALL your dates should now show up as a 5 digit number, IE 40816
it they remain as a date, that is where your error is.

MIKE

http://www.skeptic.com/


Report •

#13
September 30, 2011 at 06:32:13
The dates all changed to serial numbers. I am going to check the formatting of all of my columns to make certain there is not something hidden in the results column.

Report •

#14
September 30, 2011 at 07:29:48
Why don't you copy/paste the formula directly from your spreadsheet to a post so we can see it?

As an alternative, copy/paste this formula into your spreadsheet since it works in mine:

=IF(OR(H2>DATEVALUE("03/01/2008"),I2>DATEVALUE("03/01/2008")),"OK","OUT")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#15
September 30, 2011 at 09:18:49
=IF(OR(H3923>DATEVALUE("03/01/2008"),I3923>DATEVALUE("03/01/2008")),"OUT","OK")

Here's the copy and paste from my spreadsheet -- random row. I was checking to ensure that I was actually copying the formula down rather than merely copying the value.


Report •

#16
September 30, 2011 at 09:49:37
Here's what you posted in your original post, (with the missing comma added)

=IF(OR(H2>DATEVALUE("03/01/2008"),I2>DATEVALUE("03/01/2008")),"OK","OUT")

Here's what you say you copied from your spreadsheet:

=IF(OR(H3923>DATEVALUE("03/01/2008"),I3923>DATEVALUE("03/01/2008")),"OUT","OK")

Those 2 formulas will get you opposite answers since your value_if_true and value_if_false arguments are reversed.

However, neither of them are going to return "OUT" for every pair of values.

Don't take this the wrong way, but since you made an error in your original post (the missing comma) and the formula you most recently posted doesn't fit your criteria, it's kind of hard for us to trust what you are posting.

Are you really sure that you are getting "OUT" for every set of values? Have you really checked every result?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question