datevalue under office xp - doesn't work same as office 2000?

[Ned Hodgson]

I hope someone here can help.

I use a spreadsheet function to look at a column that
contains dates in the mm/dd/yy format. I used the
conditional sum wizard under excel 2000 to build a
formula that will look at these date values, find all
that are within a month period, and add a value in
another column if the date was within the specified
parameters.

Here's the formula; column F contains date values, and
column K contains the value to be added.

=SUM(IF($F$2:$F$149>=DATEVALUE("12/1/2003"),IF
($F$2:$F$149<=DATEVALUE("12/31/2003"),$K$2:$K$149,0),0))

This formula was built under Office 2K Professional.
When I opened the file to update it, running XP Pro and
Office XP Pro SP1, I cannot recreate the fomula for
subsequent months. I tried simply subtituting the other
datevalue targets, but it returns a value of zero, when
there are clearly dates within the column that fall
within the range or 1/1-1/31/2004. Manipulating the date
format in the formula makes no difference.

So I copy the file over to my laptop - still running
office 2000, and the formula works as expected. Worse
than that, if I save it there, and copy it back, the
formula works as expected. I have adobe acrobat macros
installed - no other add-ins except the conditional sum
wizard.

What, if anything, has changed with regard to the
datevalue operator? Is there something wrong with the
way that I am approaching the problem?

Any and all help is greatly appreciated.

 

[Don Guillett]

Did you array enter it so that after creation or editing you get {
formula }?
if not, use ctrl+Shift+Enter

 

[Don Guillett]

also. If you are using the 1904 date system under
tools>options>calculation, it will fail.

 

[Ned Hodgson]

You're way over my head. What do you mean by array
enter. Please dumb this down for me somewhat.

Thanks for all replies and assistance.

Ned

 

[Ned Hodgson]

Not using 1904 system. It looks as though the first part
of your post may have been cut off - any other thoughts?

Thanks for posting.

Ned

 

[Don Guillett]

Some formulas must be array entered. So, instead of just touching the enter
key when you type or edit a formula, hold the control key, and the shift
key and then touch the enter key. CSE