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

Hi,


I would like to know how i can calculate the number of days between two

dates that fall between another two dates (ie callender quarters)


Eg


Q1 1/1/2005 - 31/3/2005 (these dates can be variables, Q1,Q2.. etc)


1/1/2005 - 31/12/2005 What number of days between these two dates would

fall between the dates above?


Can anyone help please


Cheers!!!

If A1 has the start date and B1 has the later date then =B1-A1 will yield the
number of days if formatted as General.
--
Gary''s Student


"[email protected]" wrote:

> Hi,
>
>
> I would like to know how i can calculate the number of days between two
>
> dates that fall between another two dates (ie callender quarters)
>
>
> Eg
>
>
> Q1 1/1/2005 - 31/3/2005 (these dates can be variables, Q1,Q2.. etc)
>
>
> 1/1/2005 - 31/12/2005 What number of days between these two dates would
>
> fall between the dates above?
>
>
> Can anyone help please
>
>
> Cheers!!!
>
>

Hi Richard

One way
=MIN(DATE(2005,3,31),DATE(2005,12,31))-DATE(2005,1,1)

or with your Start date in A1, End Date in A2 and your Quarter End Date in B1

=MIN(A2,B1)-A1


Regards

Roger Govier


[email protected] wrote:
> Hi,
>
>
> I would like to know how i can calculate the number of days between two
>
> dates that fall between another two dates (ie callender quarters)
>
>
> Eg
>
>
> Q1 1/1/2005 - 31/3/2005 (these dates can be variables, Q1,Q2.. etc)
>
>
> 1/1/2005 - 31/12/2005 What number of days between these two dates would
>
> fall between the dates above?
>
>
> Can anyone help please
>
>
> Cheers!!!
>

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

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
>

Another approach to consider is to take the MAX of the start date and the
quarter start date and subtract that from the MIN of the end date and the
quarter end date.
--
Gary''s Student


"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
>
>