Forum Discussion

Shavocky's avatar
Shavocky
Copper Contributor
Mar 11, 2019

Start date and end date

Hi,   In the attached 10 year cashflow example, I'd like to have a flexible start and end date for various income and expenses. I can get the start date, but how do I write the formula to allow for...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 11, 2019

Hi,

 

End date could be calculated as

=INDEX('10 Year Cashflow'!$B$2:$DQ$2,0,MATCH($B9,'10 Year Cashflow'!$B$2:$DQ$2,0)+$C9)

if start date is in B9 and duration is in C9

 

IMHO, it'll be more flexible if you use in your timeframe not texts but dates, let say first date of each month, and format them as mmm'yy

 

  • Shavocky's avatar
    Shavocky
    Copper Contributor
    Mar 11, 2019

    Sergei,

     

    Thanks for your help and please excuse my lack of knowledge, but I have never used this type formula before. I entered the end date calculation as you suggested, but it brings back a date format answer. I probably didn't explain correctly in my initial post, but the aim of the sheet is as follows:

     

    If for Income Source 1 in the Assumptions Tab, the start date is Jan'19 and the duration is 6 months, I want the net value in F9 to run from Jan'19 to Jun'19 inclusive within the 10 year cashflow. If the start date gets changed to Feb'19 and the duration is 7 months,. the value in Assumptions F9 will run in the 10 year cashflow from Feb'19 to Aug'19 inclusive....... and so on.

     

    Is this possible? 

     

    Thanks again. 

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Mar 11, 2019

      That could be

      =IF((B$1>=MATCH(Assumptions!$B9,$B$2:$DQ$2,0))*(B$1<MATCH(Assumptions!$B9,$B$2:$DQ$2,0)+Assumptions!$C9),Assumptions!$F9,"")

      using your first helper row and assuming you have the same order of income/cost

Resources