Formula comparing dates

[Brad Sprague]

I have what I thought was going to be a very simple formula but for the life
of me I can't get it to work. I have a 3 column table with a date in the
second cell. In the 3rd cell I have a formula that is supposed to compare
the contents of cell 2 to the current date and return either Expired or
Active. No matter what date I enter, the formula always returns true. I'm
not a Word expert so any help would be appreciated. Below are some of the
formula variations that I've tried.

IF (B1 < DATE Expired Active)
IF (B1 < Now() Expired Active)
IF (CDate(B1) < DATE Expired Active)
IF (CDate(B1) < Now() Expired Active)
IF (datevalue(B1) < DATE Expired Active)
IF (datevalue(B1) < Now() Expired Active)

 

[macropod]

Hi Brad,

First problem: You can't use cell references for dates formatted as dd-mm-yy
or dd/mm/yy etc, since Word trates the contents of such cells as if they
were a formula!
First solution: Bookmark the date, but not the entire cell, then refer to
your bookmark in the IF test.

Second problem: To compare two dates, you need to express them in ISO format
(ie yyyyMMdd).
Second solution: Use a formula like{IF{=({BkMrk1 \@ yyyyMMdd}>{DATE \@
yyyyMMdd})}= 1 Expired Active}

Cheers

 

[Brad Sprague]

Thanks for the response. I tried your example but it didn't work. It looks
to me like the braces and parans are unbalanced. Could you be so kind as to
review your example and show me where it's wrong? Also, could you point me
to a good reference on Word formulas? This is the first time I've used them
and the help on this particular subject area seems pretty weak when it comes
to giving examples beyond simple single field formatting.

 

[macropod]

Hi Brad,

The code works correctly, though the ng line wraps might have messed things
up a bit for you. I take it you used Ctrl-F9 to insert the field braces -
you can't simply type them. Also, FWIW, the brackets '(' & ')' can be
deleted without doing any harm. You might also need to change the '>' to
'<', depending on which condition is meant to trigger the Expired/Active
response.

As for a 'good' reference on Word formulae, if you'd like to see what can be
done with dates & times, take a look at:
http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=wrd&Number=249902
(url all one line)
where you can download a document with may date & time calculation examples.

Cheers