Number of days in a quarter between two date ranges

[Mike]

I am trying to develop a table that will split a cost over quarters. The data
that I have is:
A B C D
E F G
Base Cost Task Start Task End Task Duration Cost per day 08Total
09Total
728.00 02-Nov-2008 31-May-2009 30.16 3.45 215.17
564.00

The total cost per year takes the percentage of time in that year and then
adds an inflation factor (which is why the total is higher than the base).
What I need to do is write a formula that will split each year into quarters.
In this example all the cost for 08 will be in Q4 etc.

Many thanks for your help

 

[Joel]

I wrote a UDF to do the calculation. You can easily convert to a formula but
it would be much more complicated.

On worksheet use

=DaysWithinDays(DATE(2008,10,1), DATE(2008,12,31), B2, C2)


In VBA add

Function DaysWithinDays(StartDate, EndDate, ActualStart, ActualEnd)

If (ActualStart > StartDate) And (ActualStart < EndDate) Then
If ActualStart < StartDate Then
ActualStart = StartDate
End If
If ActualEnd > EndDate Then
ActualEnd = EndDate
End If
DaysWithinDays = Int(ActualEnd - ActualStart)
Else
DaysWithinDays = 0
End If

End Function

 

[Bernd P]

Hello Mike,

If I understand you correctly you want to get the number of days for
each quarter for your date intervals.

Example:
For the date interval 2-Nov-2008 thru 31-May-2009 you want to see 60
days for Q4/2008, 59 days for Q1/2009 and 61 days for Q2/2009.

Put 2-Nov-2008 into cell B2
Enter 31-May-2009 into cell C2
Enter first days of each quarter into cells G1, H1, I1, etc.:
G1: 1-Oct-2008
H1: 1-Jan-2009
I1: 1-Apr-2009
J1: 1-Jul-2009

Now enter into cell G2:
=MAX(0,MIN(H1-1,$C$2)-MAX(G1,$B$2)+1)
and copy across...

Regards,
Bernd

 

[Bernd P]

Hello Joel,

I would suggest this UDF:
Option Explicit

Function DaysWithinDays(StartDate As Date, EndDate As Date, _
ActualStart As Date, ActualEnd As Date) As Long

If (ActualStart <= EndDate) And (ActualEnd >= StartDate) Then
If ActualStart < StartDate Then
ActualStart = StartDate
End If
If ActualEnd > EndDate Then
ActualEnd = EndDate
End If
DaysWithinDays = ActualEnd - ActualStart + 1
Else
DaysWithinDays = 0
End If

End Function

Regards,
Bernd