adding differences between dates

  • Thread starter Thread starter RobertK
  • Start date Start date
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
 
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.
Click to expand...

Put the following formula into C1 and copy down:

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


----- original message -----
 
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)
 
Thanks, but is there any way to do it without repeating the formula. I'm
looking for a cell formula.
 
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.
 
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.
Click to expand...
 
Back
Top