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

  • Thread starter Thread starter Ned Hodgson
  • Start date Start date
N

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.
 
Did you array enter it so that after creation or editing you get {
formula }?
if not, use ctrl+Shift+Enter
 
also. If you are using the 1904 date system under
tools>options>calculation, it will fail.
 
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
 
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
 
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
 
Back
Top