adding differences between dates
 

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

--
Robert K

adding differences between dates
 

"RobertK" wrote:
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 -----

"RobertK" wrote in message
...
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

--
Robert K

adding differences between dates
 

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)

--
Biff
Microsoft Excel MVP


"RobertK" wrote in message
...
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

--
Robert K

adding differences between dates
 

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


"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"RobertK" wrote in message
...
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

--
Robert K

adding differences between dates
 

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.

--
Biff
Microsoft Excel MVP


"RobertK" wrote in message
...
Thanks, but is there any way to do it without repeating the formula. I'm
looking for a cell formula.
--
Robert K


"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"RobertK" wrote in message
...
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

--
Robert K

adding differences between dates
 

Thanks, that did it.
--
Robert K


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"RobertK" wrote in message
...
Thanks, but is there any way to do it without repeating the formula. I'm
looking for a cell formula.
--
Robert K


"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"RobertK" wrote in message
...
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

--
Robert K

adding differences between dates
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"RobertK" wrote in message
...
Thanks, that did it.
--
Robert K


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"RobertK" wrote in message
...
Thanks, but is there any way to do it without repeating the formula.
I'm
looking for a cell formula.
--
Robert K


"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"RobertK" wrote in message
...
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

--
Robert K