Excel 2007 : Automatic updation with system date

[excellearning]

Hello Forum members,

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

In the meantime I have uploaded A tex excel file in Google Docs. Here is the link for it.

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

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.

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. Means on the 15th of each month the 2 values in red get updated and remains the same till the next month 15th when again it gets updated and so on


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 posted this question in another forum and a person gratefully helped me partly by giving me this solution.

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

The above formulas 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 18 its showing "bad date". So whats the way to work around that and keep the original current month data till the next 15th when the formula would automatically update it ? Ca the above formulas be modified to do that ?

Sorry for the trouble.


I hope my question though a bit long is clear and specific.

Thanks in advance for the help.

An Excel Learner

[dilipandey]

Hello and welcome to the forum.

I would suggest you to explore TODAY() function...
Regarding rest of the queries, you can ask them one by one so that it becomes easy to target the solution. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[excellearning]

Hello,

Thanks for the reply.

Actually its just one question but I took pains to explain to everyone what I wanted.

In short. In the attached worksheet, I need the two fields in red E4 and K4 to be automatically updated on the 15th of every month with the corresponding values in (B17:30) and (E17:E30) respectively in the Amortization table given below. When the system date in F1 is = the corresponding 15th in the table (A17: A30).

Thanks

[dilipandey]

thanks for making it short and to the point...

See the attached file where I have used the below formula to obtain the current months values from below table(s) :-

=OFFSET($B$16,MATCH($F$1,$D$17:$D$30,0),0)

TEST1.xlsm

Regards,
DILIPandey

<click on below 'star' if this helps>

[excellearning]

Dear Mr. Dili Pandey,

Thank you very much for the solution. It worked Great ! I have another question following on to that.

How can I put in G11 cell of your test worksheet the following message : "The Grand Total as of today (the system date) is = " the value shown on I11 ? For e.g. The Grand Total as of today (May 21, 2012) is = 1646389.82.

Regards,

EL

[dilipandey]

Hi EL,

Use below formula:-


="The Grand Total as of today ("&TEXT(TODAY(),"mmm dd, yyyy")&") is= "&ROUND( I11,2)

Regards,
DILIPandey

<click on below 'star' if this helps>

[excellearning]

Hello Mr. Dil Pandey,

Sorry for the late Thank You reply as I was on the move. Great Solution. Worked like a charm ! Thanks for the Great Solution.

In the same context, I have a kind of tricky question (well for a novice like me at least !). In your Test Worksheet1, Is there any way when for e.g. say the value in F1= D18 then the cells from A17:D17 is not filled with any color (last month), A18:D18 is filled in Dark Green (current month), Cells A19: D19 is filled with Red color (immediate next month) and Cells A20: D30 is filled in Yellow color (rest of future months) ? This same routine is repeated for the next month and so on till the end of table.

I tried condition formatting but failed after many attempts.

Thanks in advance for the help.

EL

[dilipandey]

Hi EL,

See the attached file with required conditional formatting.. thanks.


TEST1.xlsm
Regards,
DILIPandey

<click on below 'star' if this helps>

[excellearning]

Hello Mr. Dil Pandey,

Thanks for the solution. However there is a slight hitch in the solution. The formula in the sheet works perfect till F1 = 5-10. Then onwards it does not work for 11, 12 etc. downwards.

Thanks in advance for the help.

EL

[dilipandey]

Hi EL,

See the revised file where I have removed that hitch

PENDING-TEST1.xlsm

Regards,
DILIPandey

<click on below 'star' if this helps>

[excellearning]

Dear Mr. Dil,

Thanks for the solution. However I have 2 queries on the basis of what the formula in the sheet is doing now which need a slight modification

1. As of now the value of F1 is manually inputted ? Can this be changed so that on every 15th its automatically changed and as a consequence to that the colors are changed as well ? (and remains the same till the following month 15th when it changes again and so on )? as follows : Past month (white); current month (green); following month (red); all subsequent months till the end (yellow)

2. As of now say when we input 5 in F1 and now as today is still in May (May 26th) and not yet June 15th the A:17:A19 should be green and not white. However in the present formula the color of current month is shown as white instead of green and the next month green instead of red and so on . Ideally this current colors should change automatically ONLY when the date is 15th June and on 15th of next month and so on.

Thanks in advance for the help.

Have a great weekend.

Regards,

[dilipandey]

As of now say when we input 5 in F1 and now as today is still in May (May 26th) and not yet June 15th the A:17:A19 should be green and not white.

A17:A19 are covering May, June and July and you want that upon entering 5 in F1, these three (i.e, current month, next month and next-to-next month should be white?

I am not sure I am correctly understanding you because previously you want that current month white, next month Green, next-to-next month Red and all following month as Yellow !!!

Anyways, you can do it as per your wish as logic is very easy to customize, I have used below formula in conditional formatting where you can play with extreme right value after = sign:-
=IFERROR(DATEDIF(OFFSET($A$16,MATCH($F$1,$D$17:$D$30,0),0),$A17,"m"),0)=0
=IFERROR(DATEDIF(OFFSET($A$16,MATCH($F$1,$D$17:$D$30,0),0),$A17,"m"),0)=1
=IFERROR(DATEDIF(OFFSET($A$16,MATCH($F$1,$D$17:$D$30,0),0),$A17,"m"),0)=2
=IFERROR(DATEDIF(OFFSET($A$16,MATCH($F$1,$D$17:$D$30,0),0),$A17,"m"),0)>2

Regards,
DILIPandey

<click on below 'star' if this helps>

[excellearning]

Mr. Dil Pandey,

Sorry to make this very complicated and time consuming. May be I am trying to over help by giving too much information. Let me take you step by step.

Fundamentally, the most important point is that 15th of every month is the crucial day when AUTOMATICALLY BASED ON THE SYSTEM DATE (F1), everything changes in the worksheet (Mortgage, income expenditures, debt) etc. and NOT ON THE 1st OF EVERY MONTH.

Having said that, every month on the 15th there a mortgage due and this is shown in the Amortization table A17: D30 where A 17 is the 15th of the Month of May i.e May 15, 2012.

So emphasizing once again, automatically ONLY on the given 15th of every month (NOT ON the 1st ) in the PENDING TEST 1 Amortization table, for visual sake the following filling of the rows with below specified color should happen:

1. White (previous month) corresponding to : APRIL 15- MAY 15 ; A16: D16
2.Green (current month) corresponding to : MAY 15-JUNE 15 ; A17: D17
3. Red (next month) corresponding to : JUNE 15- JULY 15 ; A18: D18
4. Yellow (all subsequent months after red) corresponding to : JULY 15, 2012 - JUNE 14, 2013 ; A19:D30


I tried to play with the formula and modified it in the following way.


=IFERROR(DATEDIF(OFFSET($A$16,MATCH($F$1,$D$17:$D$30,0),0),$A17,"m"),0)<1 (white)
=IFERROR(DATEDIF(OFFSET($A$16,MATCH($F$1,$D$17:$D$30,0),0),$A17,"m"),0)=1 (green)
=IFERROR(DATEDIF(OFFSET($A$16,MATCH($F$1,$D$17:$D$30,0),0),$A17,"m"),0)=2 (red)
=IFERROR(DATEDIF(OFFSET($A$16,MATCH($F$1,$D$17:$D$30,0),0),$A17,"m"),0)>2 (yellow)

However, when you run these conditions for the table as of right now today ( May 28th 2012), the rows A17: D17 refuses to give me green (current month) instead it shows it as white (previous month) but the rest of the conditions work great. When I tried putting -1 as a condition for white it gives me error in the formula.

So

1. Where am I going wrong in the condition values (0,1,2) in the formula you had suggested ?

2. As of now is the condition color getting updated in the Amortization table only on the 15th of every month ? if not how should the formula be modified for incorporating that ?

FINALLY once more my apologies AGAIN for the post being long I tired my best to shorten it but failed !

Thanks a ton for your patience with me.

EL

[dilipandey]

Hi EL,

However, when you run these conditions for the table as of right now today ( May 28th 2012), the rows A17: D17 refuses to give me green (current month) instead it shows it as white (previous month) but the rest of the conditions work great. When I tried putting -1 as a condition for white it gives me error in the formula.

I would like to see above, upload your sample workbook and let's sort all points there only... thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[excellearning]

Hello Mr. Dil Pandey,

I am starting everything from scratch. Attached here with is the link for the worksheet. I have removed all the formulas and just put everything what I wanted including the color fills as I want according to the current system date.

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

Where in :

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 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 : APRIL 15- MAY 15 ; A16: D16
2.Green (current month) corresponding to : MAY 15-JUNE 15 ; A17: D17
3. Red (next month) corresponding to : JUNE 15- JULY 15 ; A18: D18
4. Yellow (all subsequent months after red) corresponding to : JULY 15, 2012 - JUNE 14, 2013 ; A19:D30


Note : I have deleted all the earlier formulas on the sheet for all the three requirements. So that you could look at the sheet afresh and suggest something for me.

Thanks.

EL

[dilipandey]

Hi EL,

Please upload a sample workbook in this forum... click on "Go advanced" and look for paper clip icon to attach . thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[excellearning]

Hello Mr. Dil Pandey,

Thanks for your reply. Attached here with is the PENDING-TEST3.XLMS worksheet. I am starting everything from scratch. I have removed all the formulas and just put everything what I wanted including the color fills as I want according to the current system date.


Where in :

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 : APRIL 15- MAY 15 ; A16: D16
2.Green (current month) corresponding to : MAY 15-JUNE 15 ; A17: D17
3. Red (next month) corresponding to : JUNE 15- JULY 15 ; A18: D18
4. Yellow (all subsequent months after red) corresponding to : JULY 15, 2012 - JUNE 14, 2013 ; A19:D30


Note : I have deleted all the earlier formulas on the sheet for all the three requirements. So that you could look at the sheet afresh and suggest something for me.

Thanks.

EL

[excellearning]

Hi Mr. Dil Pandey

Have you given up on me due to my complex questions ! ? I sincerely hope not.

Thanks.

EL

[dilipandey]

not really.... but I got confused with your changing criterias and finally surrendered to my limited knowledge/understanding

Regards,
DILIPandey

<click on below 'star' if this helps>

[excellearning]

Mr. Dil Pandey,

Well first of all thanks for the frankness. However I would not agree with your self-assessment, of excel knowledge, as you gave me solutions for which lots of other people in other forums gave up without a fight ! I still confidently believe you could still find me a solution. However, I respect your space and time and would not press you more if you are not interested.

Once more thanks for sharing with me your precious time and intellect.

I hope to get in touch with you in the future.

Till then Ciao ! and best of luck in whatever you are doing.

Thanks for your efforts on my behalf.

Regards,

EL

[dilipandey]

Thanks EL... for your understanding

cheers

Regards,
DILIPandey

<click on below 'star' if this helps>