Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
 
Posts: n/a
Default 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!!!

  #2   Report Post  
Gary''s Student
 
Posts: n/a
Default Calculating number of days between two dates that fall between two

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


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


  #3   Report Post  
Roger Govier
 
Posts: n/a
Default Calculating number of days between two dates that fall betweentwo other dates

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


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

  #4   Report Post  
TP
 
Posts: n/a
Default Calculating number of days between two dates that fall between two other dates

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

  #5   Report Post  
Roger Govier
 
Posts: n/a
Default Calculating number of days between two dates that fall betweentwo other dates

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$2B$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



  #6   Report Post  
Gary''s Student
 
Posts: n/a
Default Calculating number of days between two dates that fall between

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to find the working days difference between to dates? Mudgeman Excel Discussion (Misc queries) 2 May 15th 06 04:26 AM
how would I count dates (not # of days) in cells that fall betwee. sailingscotts Excel Worksheet Functions 3 August 16th 05 04:29 AM
Number of years, months, days between two dates. Bluenose Excel Worksheet Functions 34 June 30th 05 02:18 PM
Calculating recurring date in following month, calculating # days in that period Walterius Excel Worksheet Functions 6 June 4th 05 11:21 PM
Calculate number of days in a column of dates Barbara Excel Discussion (Misc queries) 8 May 25th 05 02:48 PM


All times are GMT +1. The time now is 03:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"