Formula Help with reference to Date

[Learner]

Hello Everyone,


Sorry for cross posting this somewhere else with no malice intentions. I have a question on making a formula with real time date function. I have built my own simple personal money management worksheet. I want to know how I can write a formula which can automatically change 1. update my monthly EMI under my monthly expenses column 2. update my cumulative mortgage amount from my total debt column, every month by the 15th for the next 5 years. I have this data for the month date, cumulative mortgage and the corresponding monthly EMI as an amortization table inside the sheet and now I am manually doing it every month which gets automatically added to the expenses and simultaneously gets adjusted from my Total Savings Debt etc. I tried lots of formulas mainly with IF but I cannot do it for more than a month.


Gross Income: B4

Net Income: B9

Mortgage: E4

Net Expenses: E9

System Date : F1 (Now () ; in format : May 16, 2012)


Amortization Table Date : A17: A 30 (12 months)(in the format: mm/dd/yy)

Amortization Table EMI : B 17 : B 30 ( I put constant EMI as data but could be variable EMI)

Amortization Table Cumulative Debt : C17: C30


Note: How can I add the Test Macro enabled excel 2007 worksheet to this post so its clear for everyone who would like to respond to what I mean and want the formula for ?


Thanks in advance for your efforts on my behalf.


Learner RM

 

[Faseeh]

Hi Learner,


You can upload a sample file on free file hosting site, and share the link here. Do remember to make it accessible to public so that any one can download it.


BTW this thread also discusses how to have cumulative/update total if new data is entered, have a look at this thread.


http://chandoo.org/forums/topic/formula-for-cumulative-total


Regards,

Faseeh

 

[Learner]

Hi Faseeh,


I looked at the link you had send me but that is not what I wanted. In the meantime I have followed your instructions and uploaded the file in Google Docs. Here is the link for it.


https://docs.google.com/open?id=0B4X1gMMzKVU9OFppcnh6RExMbFk


I hope you are able to understand what I am looking for from the file. Briefly, In that file the required fields in red color ( Date, Mortgage, Debt) need to be updated automatically with the values of EMI ( B 15 - B 30) and Outstanding (C 15- C 30) from the sample Amortization table (A-C 15; A-C 30) given below every month on the 15th. i.e when the system date (F1) = corresponding date (A 15- A30) in the Amortization table.


Thanks in advance for the help.


Learner

 

[Hui]

Learner


Do you want to calculate the GTotal values for each date in A17:C30?


If so Data Table will help you,

Have you had a look at: http://www.chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/

 

[Learner]

Hello Hui,


I want the data in Fields E4 and K4 in the excel file get automatically updated 15th of EVERY month ( when both the values of F1 and corresponding value of A17-A30 match ) with the corresponding values of "EMI" and "Outstanding" in the table (A17 -C 30) below in the Test excel sheet.


What I kind of need in real logic terms is a formula which can do the following in the test sheet:


IF Date(F1) (05/15/2012) = Date (A1)(05/15/2012) then Mortgage (E4) = EMI (B1) and Debt (K4) = Outstanding (C1) and like that 15th of the next month the sheet should update automatic i.e., IF Date(F1)(06/15/2012) = Date (A2) (06/15/2012) then Mortgage (E4) = EMI (B2) and Debt (K4) = Outstanding (C2) and so.


I appreciate your link and I had a look at the data table links in that post. However, again my impression is that the data is not automatically updating say in response from a live scenario or a particular system date etc. ? rather a manual scenario creator like What If ? I dont know I maybe wrong as well.


Thanks and hope my question is more clear and specific.


Learner

 

[Hui]

Try:

E4:

=IfError(INDEX(B17:B30,MATCH(F1,A17:A30,0)),"Bad date")

K4: =Iferror(INDEX(C17:C30,MATCH(F1,A17:A30,0)),"Bad date")

 

[Learner]

Hui,


Thanks a lot for the formula. Works like a charm FOR THAT PARTICULAR DAY 15 FOR ANY MONTH / YEAR. However problem is before or after that day. For e.g. as the date today is May 17 its showing "bad date". So whats a way to work around that and keep the original current month data till the next 15th when you formula would automatically update it ?


Sorry for the trouble.


Thanks


Learner

 

[Learner]

Hello Folks,


I am trying my luck once more for a solution to my vexed problem by continuing on my earlier postings. I have tired all sorts of things but nothing seems working.


Attached here with is the link for the test worksheet "PENDING-TEST3.XLMS ".


https://docs.google.com/open?id=0B4X1gMMzKVU9OVlKQWpYTGw3cjQ


Fundamentally, the most important point is that 15th of every month is the crucial day, when AUTOMATICALLY BASED ON THE SYSTEM DATE (F1), 3 specific cells changes in the attached worksheet should occur.


Firstly E4 : changes with values from B 16: B 30 of that month on the 15th and remains the same till next 15th


Secondly K4 : Changes with values from C 16: C30 of that month on the 15th and remains the same till next 15th


Thirdly in the Amortization table when on 15th of the given month till the 15th of next month, for visual sake the following filling of the rows with below specified color should happen:


1. White (previous month) corresponding to : MAY 15-JUNE 15 ; A17: D17

2.Green (current month) corresponding to : JUNE 15- JULY 15 ; A18: D18

3. Red (next month) corresponding to : JULY 15- AUG 15 ; A19: D19

4. Yellow (all subsequent months after red) corresponding to : AUG 15, 2012 - JUNE 14, 2013 ; A20:D30.


I tried playing around with the instructions and the attached worksheet of Chandroo's post on the coloring on condition. Unfortunately I cannot get a solution with my limited knowledge.


I hope someone can help me with this

 

[Hui]

E4: =IFERROR(INDEX(B16:B30,MATCH(F1,A16:A30,1)),"Bad date")

K4: =IFERROR(INDEX(C16:C30,MATCH(F1,A16:A30,1)),"Bad date")

 

[Haseeb A]

Hello Learner,


E4: =IFERROR(LOOKUP(F1,A16:B30),"< Date")

K4: =IFERROR(LOOKUP(F1,A16:C30),"< Date")


Conditional Format:


Select A16:C30. make sure A16 is active cell. for this start to select from A16, so start cell will be active cell.


Green:


=TEXT($A16,"myyyy")=TEXT($F$1-14,"myyyy")


Red:


=TEXT($A16,"myyyy")=TEXT(EOMONTH($F$1-14,1),"myyyy")


Yellow:


=AND(N($A16),$A16>EOMONTH($F$1-14,1))


Hope this helps.

 

[Learner]

Thanks Hui and Haseeb for the help. The formulas worked as requested.


However, for a learner like me could you explain to me what the number 14 does in the formula ? Is it subtracting F1 day from A16 day date ? I assumed by manipulating that number (14) to say 5,10 etc. I get the condition to work on any day of the month ? Just to check that assumption, I removed the 14 in the formula and ran the condition and it changed the visualization to the 1st of the month instead of the 15th. The results surprisingly did not change even when I changed the date sequence of A16:A30 from 4/15/2012 - 6/15/2012 to 4/1/2012 - 6/1/2013 downwards. So iam confused and more curious to know how the formula worked, as my first assumption was proved wrong.


Another question is in the formula syntax for Yellow color condition; =AND(N($A16),$A16>EOMONTH($F$1-14,1)). Is "N" inside just a typo ? I deleted that and the formula worked fine without it.


Thanks for your time.