adding differences between dates

[RobertK]

I have 2 column A & B. Both columns have 100s of rows but they may not all
be filled, many will be blank. I want to know the number of days there are
(total) betwen the two columns when there is an entry in both columns plus
the numbers of days there are from today (total) when only column A has a
date. If column B has a date column A will also have a date.

Example

A B
1/2/09 2/3/09
3/6/08

6/7/09 7/4/09

4/5/08

3/6/09 3/30/09

 

[JoeU2004]

I want to know the number of days there are (total) betwen
the two columns when there is an entry in both columns plus
the numbers of days there are from today (total) when only
column A has a date.

Put the following formula into C1 and copy down:

=if(AND(A1<>"",B1<>""), B1 - A1, if(A1<>"", today() - A1, ""))


----- original message -----

 

[T. Valko]

Try this entered in column C:

=IF(A2<>"",IF(B2<>"",B2-A2,TODAY()-A2),"")

Copy down as needed, then to get a grand total:

=SUM(C2:C100)

 

[RobertK]

Thanks, but is there any way to do it without repeating the formula. I'm
looking for a cell formula.

 

[T. Valko]

Try this array formula** :

=SUM(IF(A2:A100<>"",IF(B2:B100<>"",B2:B100-A2:A100,TODAY()-A2:A100)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[RobertK]

Thanks, that did it.
--
Robert K

Try this array formula** :

=SUM(IF(A2:A100<>"",IF(B2:B100<>"",B2:B100-A2:A100,TODAY()-A2:A100)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

You're welcome. Thanks for the feedback!