Dates in Formulas (Excel 97)

Topic

New Reply

Hello,

I’m encountering a problem.

I have a list of dates in column S

The criteria is as follows
if the date is before 12/31/02 then blank
if the date is after 12/29/03 than 2004
all else 2003

I entered the following fomula

=IF(S2″12/29/2003″,”2004″,”2003″))

I get all blanks. I’m not all that familiar with how to work dates in formulas. Can someone give me a push in the right direction?

Reply | Quote

Replies

Hi,

You can’t use dates directly in formulae. Try:
=IF(S2DATEVALUE(“12/29/2003”),2004,2003))

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

That worked!

Thank you so very much!

Reply | Quote

Well, I am now finding inaccurate data.

The fomula I am using in VBA is:
“=IF(RC[-3]DATEVALUE(“”12/29/2003″”),””2004″”,””2003″”))”

I have the following dates reporting as 2004:
1/6/03
1/22/03
12/30/02

12/28/02 is coming back as 2004…

Hmmm, anyone have any help?

Reply | Quote

This doesn’t answer your question, but why not just use the formula:

=year(s2)

Steve

Reply | Quote

I thought of that also, but if you look at the formula closely, it returns 2004 for the last two days of 2003. If that is what is really wanted, your formula does not work.

Reply | Quote

Legare,
Good Catch on both counts!
That’s what I get for trying to guess what they wantrather than what they ask for.
I am surprised that a text value is greater than a date. I thought the text would be “0” and < date.

It must be compared as text AND it must use as TEXT for the number a formatted value.

Both seem odd to me, though I have given up trying to understand all the quirks in excel.

Steve

Reply | Quote

The text string probably forces a byte by byte comparison of the characters in the date string to the binary bytes of the date value. The first that are not equal sets the result.

Reply | Quote

I can’t use the last 2 numbers in the year because 2002 ends on 12/30/02 (Therefore anything done on or before 12/30/02 falls in 2002)
2003 starts on 12/31/03 and ends on 12/29/03. Anything on or after 12/30/03 falls in 2004.

I just reentered the formula directly into a cell and it works fine, it seems to be a problem in VBA. Does the DATEVALUE work differently in VBA?

Directly in Cell:
=IF(S2DATEVALUE(“12/29/2003″),”2004″,”2003”))

VBA:
Range([t2], [t2].End(xlDown)).Offset(0, 2).FormulaR1C1 = _
“=IF(RC[-3]DATEVALUE(“”12/29/2003″”),””2004″”,””2003″”))”

Reply | Quote

Putting this in V2 and copying it down
=IF(S2DATEVALUE(“12/29/2003″),”2004″,”2003”))

Is identical to the VB code.

The problem is (as Legare had mentioned) is that some of your “dates” In col S are NOT dates but TEXT that looks “like a date”

Steve

Reply | Quote

I have found the error of my ways.. I am to embarrased to tell you what I did, but I will say it wasn’t because of text in my column…

I hate when I make stupid mistakes, they are so time consuming!

Reply | Quote

Everyone would really appreciate knowing what the problem was so we can diagnose or avoid the same problem in the future.

Reply | Quote

The problem was completly my fault.

I was changing the code in a part of the module that I had already run. So every time I ran the second part of my code the dates never changed ’cause, duh!, the code was in the module that I had already run

Reply | Quote

I used VBA to enter the formula and as long as what is in RC[-3] is a date value, it works. However, if what is in RC[-3] is text that looks like a date, then I get the results that you describe.

Reply | Quote

Oops.. I fogot to thank you guys for all of you help!

Thank you!

Reply | Quote

That formula works fine for me if the cells contain dates. However, if the cells contain text strings that look like dates, then the formula will always return 2004.

Reply | Quote