Calculating with dates

M

muchacho

Apologies if this should be in the section for new users.

What I have are many rows with a 'from date' (ColumnA) and a 'to date'
(columnB). Now what I want is at the end of each row, to display how
many days of data is covered.

For example 1/1/01 (cell A1)to 2/1/01 (cell B1) would be 2 , as it has
2 days worth of data (the 1st and 2nd). Now what I've been doing is
something like (B1-A1)+1=Answer (answer in C1)

but what happens, is the rows that are blank (e.g A235 and B235) result
in 1, as it's doing the sum (0-0)+1.

On another worksheet I want a total. All the C column is being added so
that I have an overal total for the whole worksheet.

The question is: How can I get it so that it ignores all these 1's and
the only time it adds a 1 is when there is a valid 'from' and 'to'
date. Eg. 23/4/01 to 23/4/01 ?

Any help would be appreciated. If I've confused anybody let me know.

Cheers.
 
M

muchacho

I think I cracked it ... I used this formula.

A1,2,3,4 etc is the 'from date' and B1,2,3,4 etc is the 'to date'

=IF(AND(A1<>0,B1<>0),B1-A1+1,"")

Hopefully this will produce a valid result when dates are in and a
blank result when the date fields are blank.
 
K

KellTainer

Just check if the other two fields are filled or not.

=IF(AND(A1<>"", B1<>""),B1-A1+1,"")
 
Top