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.

You are missing a comma between "OK" and "OUT"

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

Thanks, I do have that in my excel formula -- just missed it in the post.

Are you sure all your Dates in column H & I are all realdates?

Not just text strings thatlooklike dates?MIKE

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.

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.

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.

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.

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

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/2008So, 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.

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.

Your example is correct. I, however, get "OUT" for every row.

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 NumbersALL 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

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.

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.

=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.

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_trueandvalue_if_falsearguments 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.

Ask Your Question

Weekly Poll

Would you use Amazon to buy airline tickets?

Discuss in The Lounge

Poll History