Results 1 to 15 of 16
Thread: Dates in Formulas (Excel 97)

20030206, 23:00 #1
 Join Date
 Mar 2001
 Location
 San Clemente, California, USA
 Posts
 130
 Thanks
 2
 Thanked 0 Times in 0 Posts
Dates in Formulas (Excel 97)
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/31/2002","",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?

20030206, 23:22 #2
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,136
 Thanks
 2
 Thanked 441 Times in 363 Posts
Re: Dates in Formulas (Excel 97)
Hi,
You can't use dates directly in formulae. Try:
=IF(S2<DATEVALUE("12/31/2002"),"",IF(S2>DATEVALUE("12/29/2003"),2004,2003))
CheersCheers,
Paul Edstein
[MS MVP  Word]

20030206, 23:26 #3
 Join Date
 Mar 2001
 Location
 San Clemente, California, USA
 Posts
 130
 Thanks
 2
 Thanked 0 Times in 0 Posts
Re: Dates in Formulas (Excel 97)
That worked!
Thank you so very much!

20030207, 23:28 #4
 Join Date
 Mar 2001
 Location
 San Clemente, California, USA
 Posts
 130
 Thanks
 2
 Thanked 0 Times in 0 Posts
Re: Dates in Formulas (Excel 97)
Well, I am now finding inaccurate data.
The fomula I am using in VBA is:
"=IF(RC[3]<DATEVALUE(""12/31/2002""),""2002"",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?

20030207, 23:58 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Dates in Formulas (Excel 97)
This doesn't answer your question, but why not just use the formula:
<pre>=year(s2)
</pre>
Steve

20030208, 00:03 #6
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Dates in Formulas (Excel 97)
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.
Legare Coleman

20030208, 00:05 #7
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Dates in Formulas (Excel 97)
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.
Legare Coleman

20030208, 00:35 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Dates in Formulas (Excel 97)
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

20030208, 00:36 #9
 Join Date
 Mar 2001
 Location
 San Clemente, California, USA
 Posts
 130
 Thanks
 2
 Thanked 0 Times in 0 Posts
Re: Dates in Formulas (Excel 97)
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(S2<DATEVALUE("12/31/2002"),"2002",IF(S2>DATEVALUE("12/29/2003"),"2004","2003"))
VBA:
Range([t2], [t2].End(xlDown)).Offset(0, 2).FormulaR1C1 = _
"=IF(RC[3]<DATEVALUE(""12/31/2002""),""2002"",IF(RC[3]>DATEVALUE(""12/29/2003""),""2004"",""2003""))"

20030208, 00:43 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Dates in Formulas (Excel 97)
Putting this in V2 and copying it down
=IF(S2<DATEVALUE("12/31/2002"),"2002",IF(S2>DATEVALUE("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

20030208, 00:52 #11
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Dates in Formulas (Excel 97)
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.
Legare Coleman

20030208, 00:56 #12
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Dates in Formulas (Excel 97)
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.
Legare Coleman

20030208, 00:57 #13
 Join Date
 Mar 2001
 Location
 San Clemente, California, USA
 Posts
 130
 Thanks
 2
 Thanked 0 Times in 0 Posts
Re: Dates in Formulas (Excel 97)
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... <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
I hate when I make stupid mistakes, they are so time consuming!

20030208, 00:58 #14
 Join Date
 Mar 2001
 Location
 San Clemente, California, USA
 Posts
 130
 Thanks
 2
 Thanked 0 Times in 0 Posts
Re: Dates in Formulas (Excel 97)
Oops.. I fogot to thank you guys for all of you help!
Thank you!

20030208, 01:58 #15
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Dates in Formulas (Excel 97)
Everyone would really appreciate knowing what the problem was so we can diagnose or avoid the same problem in the future.
Legare Coleman