Formula Worked previously and now shows #value!

[spencerf_iii]

I'm using the following formula to display the SUM number of days fom within a a series of dates

=SUM(DATEDIF(A2:A23,B2:B23,"d"))

for example Column A has the start date and Column B has the end date.

9/1/2009 10/1/2009
10/1/2009 10/31/2009
10/17/2009 10/24/2009
10/24/2009 10/31/2009
11/7/2009 11/14/2009
11/10/2009 11/19/2009
12/25/2009 12/31/2009
12/25/2009 1/1/2010

The above formula had worked previously but now when I try to use the formula instead of showing the total number of days I get #VALUE!

If i click on the equal sign in front of my formula the box that pops up shows the correct sum that my formula should display but for some reason it's not displaying the number down in the cel.

I'm running Excel 2000 on windows XP

[NBVC]

Is there any text values in the ranges or spaces entered with spacebar?

[daddylonglegs]

That formula is an "array formula" and needs to be confirmed with CTRL+SHIFT+ENTER

select cell with formula then press F2 key to select formula and then hold down CTRL and SHIFT keys and press ENTER. If done correctly you should see curly braces like { and } around the formula.

You can avoid "array entering" by using SUMPRODUCT, i.e.

=SUMPRODUCT(DATEDIF(A2:A23,B2:B23,"d"))

....or you probably don't need DATEDIF - for dates you can just subtract one from the other, i.e.

=SUMPRODUCT(B2:B23-A2:A23)

[spencerf_iii]

That formula is an "array formula" and needs to be confirmed with CTRL+SHIFT+ENTER

select cell with formula then press F2 key to select formula and then hold down CTRL and SHIFT keys and press ENTER. If done correctly you should see curly braces like { and } around the formula.

You can avoid "array entering" by using SUMPRODUCT, i.e.

=SUMPRODUCT(DATEDIF(A2:A23,B2:B23,"d"))

....or you probably don't need DATEDIF - for dates you can just subtract one from the other, i.e.

=SUMPRODUCT(B2:B23-A2:A23)

All three of those fixes worked for me. Thank you very much!