Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2015
    Posts
    2

    Unanswered: Quarterly Forecast for Multiple Projects with Varying Project Start/ End dates

    I have several projects that each have different start spending and end spending dates.
    The customer has asked to see a quarterly spending plan for each project, summarizing how much money may be forecasted for expenditure each quarter.
    Is there a function to say: yes or no, part of "Q1 2016" falls within the project spending start/ end dates?

    For example:
    Project 1: [Begin Spending] 1/14/2016, [End Spending] 6/12/2016
    Project 2: [Begin Spending] 4/14/2016, [End Spending] 9/12/2016

    The final report should look like this:

    ..................2015 Q4.......2016 Q1....2016 Q2......2016 Q3.....2016 Q4...2017 Q1 ......
    Project 1.........No..............Yes.........Yes......... .....No.............No..........No
    Project 2.........No..............No...........Yes........ .....Yes ..........No...........No

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is there q function, no
    is there way of doing this, most certainly

    you need a way of translating dates into accounting periods
    how you do that is upto you, but its quite common to have a table whicb defines an accounting caledar. something like
    financial_year
    period_ref
    starts_on

    ..or ends on. be wary of having both a starts on and ends on date as its possible that users can screw up the data. say by defining one period as endign after another one has started, or missing some dates altogether.

    ..then join your financial data to that tabel to fidn the relevant year and period ID
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2015
    Posts
    2
    Thanks for the tip!

    I found an argument in another thread that makes perfect sense to do a simple true/ false.
    [StartDate1]<=[EndDate2] AND [StartDate2]<=[EndDate1]

    Here I plugged in dates for a quarter

    =IIf([Const - Begin Spend]<=#12/31/2016# And #9/30/2016#<=[Const - End Spend],([Const-Budget]/(DateDiff("q",[Const - Begin Spend],[Const - End Spend]))),"")

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    aaaaargh
    you are hard coding dates... never, EVER a good idea, unless this is a one off
    buut if yuou have multiple periods then hardcodinmg is a real problem. think long and hard about the calendar table approach
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •