Using dates dynamically

[CarlisleSol]

Good Day

I hope this email finds you well.

I am having issues with a phase in spreadsheet. More specifically minipulating the timeframes/dates on the spreadsheet.

The Rule is as follows:

Cash balance lower than 15K = invest immediately. Therefore 1 phase-in period etc see formula in column H.

Now for 3 period phase in. These phase inns have to happen every alternative month accross a six month period. If the period is 01 Sept 2020 to 01 Feb 2021 then trades have to be executed in Sept, Nov, Jan. How do I create a formula that makes my phase in amount "0" Zero for months that trades do not take place (i.e. Oct, Dec, Feb).

Your advice will be appreciated.

Kind Regards,

Carlisle Solomon 

[#2]

@CarlisleSol 

Per my understanding, you need to build a stream of cash flows going out in the future which would have a series of dates and you need to figure out which months should have investment and which months should not. Let me know if the understanding is different.

I assume you have dates on the top of your table for which we would determine if the investment should happen or not. It can be done by using the MOD function and checking the result with divisor 2 for getting payments in alternate months.

=IF(MOD(MONTH(L$1)-MONTH($F3), 2)=0, "Invest", 0)

=IF(MOD(MONTH(Date of the period which needs to be tested)-MONTH(date of the first cash flow), 2)=0, Investment Amount , 0)

I have added the formula for one of the records in your spreadsheet. See attached.

You would replace the "Invest" in the formula with the amount you want to be invested.

Please let me know if this is the required result or else please explain the issue further.

If this gives you the required result, please vote and mark this post.

[#3]

@himanshu_nassa 

Good Afternoon 

Thank you for your previous assistance. I have a attached my attempt at using your formula, for some reason my investments are being dispersed in the incorrect months. Kindly see excel sheet attached, I have placed notes in order to provide some guidance.

Could you kindly advise what I am doing incorrectly in my formula.

Your assistance will be appreciated.

Kind REgards,

Carlisle Solomon 

[#4]

@CarlisleSol - The formula in H3 is comparing the current date with End date instead of the Start Date.

Check out the attached file with the correction.

Thanks,

Himanshu

http://sipnlearn.in