Using dates dynamically

%3CLINGO-SUB%20id%3D%22lingo-sub-1668400%22%20slang%3D%22en-US%22%3EUsing%20dates%20dynamically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1668400%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Day%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20email%20finds%20you%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20issues%20with%20a%20phase%20in%20spreadsheet.%20More%20specifically%20minipulating%20the%20timeframes%2Fdates%20on%20the%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Rule%20is%20as%20follows%3A%3C%2FP%3E%3CP%3ECash%20balance%20lower%20than%2015K%20%3D%20invest%20immediately.%20Therefore%201%20phase-in%20period%20etc%20see%20formula%20in%20column%20H.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20for%203%20period%20phase%20in.%20These%20phase%20inns%20have%20to%20happen%20every%20alternative%20month%20accross%20a%20six%20month%20period.%20If%20the%20period%20is%2001%20Sept%202020%20to%2001%20Feb%202021%20then%20trades%20have%20to%20be%20executed%20in%20Sept%2C%20Nov%2C%20Jan.%20How%20do%20I%20create%20a%20formula%20that%20makes%20my%20phase%20in%20amount%20%220%22%20Zero%20for%20months%20that%20trades%20do%20not%20take%20place%20(i.e.%20Oct%2C%20Dec%2C%20Feb).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20advice%20will%20be%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20Regards%2C%3C%2FP%3E%3CP%3ECarlisle%20Solomon%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1668400%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1670131%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20dates%20dynamically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1670131%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F794202%22%20target%3D%22_blank%22%3E%40CarlisleSol%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPer%20my%20understanding%2C%20you%20need%20to%20build%20a%20stream%20of%20cash%20flows%20going%20out%20in%20the%20future%20which%20would%20have%20a%20series%20of%20dates%20and%20you%20need%20to%20figure%20out%20which%20months%20should%20have%20investment%20and%20which%20months%20should%20not.%20Let%20me%20know%20if%20the%20understanding%20is%20different.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20assume%20you%20have%20dates%20on%20the%20top%20of%20your%20table%20for%20which%20we%20would%20determine%20if%20the%20investment%20should%20happen%20or%20not.%20It%20can%20be%20done%20by%20using%20the%20MOD%20function%20and%20checking%20the%20result%20with%20divisor%202%20for%20getting%20payments%20in%20alternate%20months.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(MOD(MONTH(L%241)-MONTH(%24F3)%2C%202)%3D0%2C%20%22Invest%22%2C%200)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(MOD(MONTH(Date%20of%20the%20period%20which%20needs%20to%20be%20tested)-MONTH(date%20of%20the%20first%20cash%20flow)%2C%202)%3D0%2C%20Investment%20Amount%20%2C%200)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20added%20the%20formula%20for%20one%20of%20the%20records%20in%20your%20spreadsheet.%20See%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20would%20replace%20the%20%22Invest%22%20in%20the%20formula%20with%20the%20amount%20you%20want%20to%20be%20invested.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20if%20this%20is%20the%20required%20result%20or%20else%20please%20explain%20the%20issue%20further.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20this%20gives%20you%20the%20required%20result%2C%20please%20vote%20and%20mark%20this%20post.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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 

 

 

 

1 Reply
Highlighted

@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.