Re: Calculating number of days between two dates that fall between two other dates

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi

Then try this
In cells B1:J1 enter 01/01/05, 31/03/05, 30/06/05, etc. through to 31/12/06
In cell A2 enter your start date 01/06/05
In cell A3 enter your end date 31/05/06

In cell C3 enter the following
=(MIN(C$1,$A$3)-B$1)*--($A$2<=B$1)*--($A$3>=B$1)+(C$1-$A$2)*--($A$2<C$1)*($A$2>B$1)
Copy across through D3:J3
This will then show the number of days falling in each Quarter
If you want, you can enter the following in cell B3 to give the total duration
=SUM(C3:J3)

Clearly you can extend the range of quarters on for further years, or following the same principle you could put your start and end dates on the same line by inserting a new column B and putting the end date into B2. Just change all references from $A$3 to $B$2.

Regards

Roger Govier


TP wrote: 
Roger,

I cant get either of the above to work. I am trying to calculate the
number of days in one range that fall in another range. It is for the
purposes of insurance, there are several policies that start at
different time throughout the year, but for accounting purposes the
quarters remain rigid to the cal. year.

so how do i calcualate how many days fall in q1 of2 006 from a policy
that say incepts on the 1/6/05 and expires on 31/05/2006

Thanks for all your help

.

Relevant Pages

  • If and Lists problems
    ... I am working on configuring an expense report that all of my companies ... locations can use, problem with this that for accounting purposes, each ... Cell M5 contains a cell validated list, if corporate is chosen then I ...
    (microsoft.public.excel.worksheet.functions)
  • Re: If and Lists problems
    ... could you provide the algorithm behind? ... Frank Kabel ... > locations can use, problem with this that for accounting purposes, ... > Cell M5 contains a cell validated list, if corporate is chosen then I ...
    (microsoft.public.excel.worksheet.functions)
  • Re: How to remove #value! as the default
    ... > value of 1 in a cell selected and the dollar amounts in that rage ... > would enter in another cell for accounting purposes. ... > a formula in that cell to get it's value on another spred sheet, ...
    (microsoft.public.excel.worksheet.functions)
  • How to remove #value! as the default
    ... I have entered a formula in a cell which requires another cell to have ... would enter in another cell for accounting purposes. ... a formula in that cell to get it's value on another spred sheet, ... row 2 if I enter the value of 1 the payment is 40.00 ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Smoked Salmon.
    ... Animals have protein cell walls that are hugely flexible and elastic. ... plants have rigid cellulose walls with a plasma ... Animal cells have no rigid structure so in a hypertonic solution they will ...
    (uk.business.agriculture)