Date Formulas for budget "burn rate"

[Cydney]

I'm trying to calculate the budget "burn rate" by quarter.

Is there a formula to determine the first date and last dates of the quarter
given a date such as 11/15/05? We know that to be 4th quarter of the current
year (10/1/05 - 12/31/05), but I need my query to determine that for me given
any date of any year.

From that, I need to determine how many days between the end date and my
"11/15/05" date using that to find out how much of my budget will be "burned"
during those days.

The remainder of my budget will be burned out by the 3 remaining quarters
(the remainder going to "beyond") until the end of the project.

Is there an example of this anywhere?

 

[SteveS]

I'm trying to calculate the budget "burn rate" by quarter.

Is there a formula to determine the first date and last dates of the quarter
given a date such as 11/15/05? We know that to be 4th quarter of the current
year (10/1/05 - 12/31/05), but I need my query to determine that for me given
any date of any year.

From that, I need to determine how many days between the end date and my
"11/15/05" date using that to find out how much of my budget will be "burned"
during those days.

The remainder of my budget will be burned out by the 3 remaining quarters
(the remainder going to "beyond") until the end of the project.

Is there an example of this anywhere?

Hi Cydney,

I'm not exactly sure what the end result is that you are looking for, but maybe
this will get you headed in the right direction.


First you have to calculate what quarter the date is in, then find out how many
days are left in the quarter.

Create a standard module then paste in the following code:

'**************************************************
' Function to calculate the days left in a quarter
' from a given date
'**************************************************
Public Function DaysLeftInQuarter(SomeDate As Date) As Integer
Dim WhichQuarter As Integer
Dim EOQ As Date ' EOQ = end of quarter

WhichQuarter = DatePart("q", SomeDate)

'calc end of quarter date
Select Case WhichQuarter
Case 1
EOQ = DateSerial(Year(SomeDate), 4, 0)
Case 2
EOQ = DateSerial(Year(SomeDate), 7, 0)
Case 3
EOQ = DateSerial(Year(SomeDate), 10, 0)
Case 4
EOQ = DateSerial(Year(SomeDate), 12, 31)
End Select

DaysLeftInQuarter = DateDiff("d", SomeDate, EOQ)

End Function
'**************************************************

If there is a date column in the query named "BudgetDate", in a blank column of
the query enter:

BurnaysLeftInQuarter(BudgetDate)


This will return the days left in the quarter.

HTH

 

[David C. Holley]

I would create a table named tblQuarters and load up the values there. I
would do this because its entirely possible that the definition of a
quarter might change at which point you'd have to go back and recode
things. By using a table you would then just use DLookups to grab the data.

 

[Cydney]

You're my new best friend, Steve!
I think I was making the whole problem more complicated.. The hazards of
knowing the whole "big picture" problem, I think... I had almost gotten there
the same way you did, it was the last formula (DaysLeftInQuarter =
DateDiff("d", SomeDate, EOQ) ) that I hadn't quite gotten a handle on...
thanks a bunch!

 

[SteveS]

You're my new best friend, Steve!
I think I was making the whole problem more complicated.. The hazards of
knowing the whole "big picture" problem, I think... I had almost gotten there
the same way you did, it was the last formula (DaysLeftInQuarter =
DateDiff("d", SomeDate, EOQ) ) that I hadn't quite gotten a handle on...
thanks a bunch!

Working with dates can be tricky.... I've spent lots of time trying to get a
date calculation to work.

Glad you've got it working.