Home

Start date and end date

Shavocky
New Contributor

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 a flexible end date? 

 

In the attached example, given that Income Source 1 starts in Jan'19, how can I get this to be flexible, allowing it to end anytime from 2 to 120 months later? 

 

Thanks in advance. 

3 Replies

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

 

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. 

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies