R

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

J

JoeU2004

RobertK said:
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

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)

R

RobertK

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

T

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.

R

RobertK

Thanks, that did it.
--
Robert K

T. Valko said:
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.