Inclusive number of days and running total

C

Chris

Hi,
I have a column of start dates (B) and a column of end dates (C).
I would like to have the inclusive total of days.

I would then like to have a running total of days for the callendar year as well as the total for any year and a grand total.

Have been playing with DATEDIF but no luck so far.

Cheers,

Chris
 
B

Bob Phillips

Just subtract the date in C from the date in B and format as General.

Then SUM as required.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Chris said:
Hi,
I have a column of start dates (B) and a column of end dates (C).
I would like to have the inclusive total of days.

I would then like to have a running total of days for the callendar year
Click to expand...
as well as the total for any year and a grand total.
 
C

Chris

Hi, Bob,
Unfortunately this doesn't give the inclusive number of days (18/1/04 - 18/1/04) gives the answer 0. For inclusive, it should be 1.
WhenI try to +1 at the end of the DATEDIF, it doesn't work.

Chris
 
B

Bob Phillips

Just ad 1 to the formula if you want inclusive.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Chris said:
Hi, Bob,
Unfortunately this doesn't give the inclusive number of days (18/1/04 -
Click to expand...
18/1/04) gives the answer 0. For inclusive, it should be 1.
 
E

Earl Kiosterud

Chris,

=C2-B2 + 1, copied down. It will likely format itself for a date
(meaningless), so format the formula cell for an ordinary number -- Format -
Cells - Number:

General
Number

For totals, sum the formula column
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Chris said:
Hi,
I have a column of start dates (B) and a column of end dates (C).
I would like to have the inclusive total of days.

I would then like to have a running total of days for the callendar year
Click to expand...
as well as the total for any year and a grand total.
 
C

Chris

Earl,
That worked - thanks.
Now the problem is that I have some data missing in either column B or C which gives invalid data in D (inclusive total days) for that row. How do I inhibit it from making an invalid calculation (it works out from yr1900!)

A knock-on from that is the ability to have a running total that doesn't become invalid when one of the cells contains an invalid calculation. How?

Cheers and many thanks to all you Excel wizards!

Chris
 
E

Earl Kiosterud

Chris,

=IF(AND(B2>0,C2>0),C2-B2+1,"")
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Chris said:
Earl,
That worked - thanks.
Now the problem is that I have some data missing in either column B or C
Click to expand...
which gives invalid data in D (inclusive total days) for that row. How do I
inhibit it from making an invalid calculation (it works out from yr1900!)
A knock-on from that is the ability to have a running total that doesn't
Click to expand...
become invalid when one of the cells contains an invalid calculation. How?
 
Top