Date and Month update

guitarist_wo

New Member
Joined
Sep 20, 2006
Messages
7
Ok, I have a finances spreadsheet that I want to put all of my expenses during a given month. I want to be able to automatically update a cell with the month depending on the date I enter in to a different cell. For example, I enter a date in a cell (9/20/06). To the right of that under my "Bills" column, I would enter in $50. Then, the next row, 5 days later I enter this date (9/25/06) with a payment of $30 in the column to the right. Then, my next payment is next month (10/5/06) of $70.

I want to update a different part of my spreadsheet automaticaly that has the total montly payments. So, for that part of the spreadsheet under the September row for "Bills", I would have a total of $80. For the October row, I would have $70. In words, I want the formula to say this: "If this date in this cell equals the month of 9, then I want this cell, which is to the right of my date that holds the amount, to update in the September row under my Bills column". I'm not quite sure how to do that and whether it has to be a macro or just a formula. Its hard to explain exactly what I want, but I hope my description helps. Any help would be greatly appreciated. THANKS!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

guitarist_wo

New Member
Joined
Sep 20, 2006
Messages
7
alright, here is a better explanation:
189074518.jpg


I want to enter a date into A16 and then an amount into B16 and have it update automatically into B3. So, as I have it in the picture, I want to add up all of the amounts for October into B3, all of the amounts paid in November into B4, etc. I need something that can read the date in column A row 16-26, once it is entered, and be able to transfer that to the name of the month above (i.e. 10/20/06 and 10/25/06 point to the month of October). Then add up everything from the same months from columns B, C, and D, to the corresponding month row above. I'm still not sure if this is possible with excel, but would like to try it out if I can.
 

macajm

New Member
Joined
Oct 21, 2005
Messages
32
In the Bills section, add the following formula in column c16 (& copy down):-
c16=year(a16)*100+month(a16)
this will produce numbers like 200611, 200611 etc.
Then in the Finances section enter the months as say first day of month and custom format as "Mmmm-yy".
repeat the formula from c16 in c3:c10.
Then enter the following formula in cell b3 and copy down to b10 (note $ refs):-
b3sumif(c$16:c$26,c3b$16:b$26)
 

guitarist_wo

New Member
Joined
Sep 20, 2006
Messages
7
ok, that works GREAT! :biggrin: but i found a problem. if i want to enter, say, 2 payments on separate rows (A16 and A17) for the month of september, then the formulas do not update the finances section correctly. it looks like it takes one row from the bills section, for every one row of the finances section.

thanks for the help.
 

guitarist_wo

New Member
Joined
Sep 20, 2006
Messages
7

ADVERTISEMENT

in further response...taking the sum of B16 and B17 and throwing it to B3. Then taking the sum of B18, B19, and whatever other payments are in November, and throwing the sums of those cells to B4. That is the general idea of how I would like it to work.
 

macajm

New Member
Joined
Oct 21, 2005
Messages
32
I note in your example spreadsheet that one of your dates is 2005 not 2006 so this may have something to do with the fact that it is not accumulating into the listed month.
:oops: Also note that I assume you realised that I left out the = in the line
b3=sumif(c$16:c$26,c3b$16:b$26)
 

macajm

New Member
Joined
Oct 21, 2005
Messages
32

ADVERTISEMENT

I note in your example spreadsheet that one of your dates is 2005 not 2006 so this may have something to do with the fact that it is not accumulating into the listed month.
Also note that I assume you realised that I left out the = in the line
b3=sumif(c$16:c$26,c3,b$16:b$26)
 

guitarist_wo

New Member
Joined
Sep 20, 2006
Messages
7
yeah, i got the "=" in there. the 2005 in there did not matter either. i changed it to 2006 and it would still not total the monthly payments into one cell in the "finances" sections. it put each payment across multiple cells in the "finances" section. i would like to get all of the october payments into cell B3 (meaning: sum up B16, B17, and whatever other payments occur in october to go into B3), all of the november payments into B4, and so on, no matter how many payments i have per month, whether it be 30 or just 1. is that possible?
 

guitarist_wo

New Member
Joined
Sep 20, 2006
Messages
7
this is what i have so far and it is updating wrong in the "Finances" section, column B.

189121078.jpg


I need B16 and B17 to sum up into B3. And B18:B21 needs to sum up into B4. And so on for the rest of the months.
 

macajm

New Member
Joined
Oct 21, 2005
Messages
32
looks like you have a problem with the formulae in c4 to c10
c3 seems to produce the right result, so if you have
c3=YEAR(A3)*100+MONTH(A3), you should be able to copy that formula down into c4 to c10
If you still have problems send me your email & I will send you my test spreadsheet.
 

Forum statistics

Threads
1,136,345
Messages
5,675,223
Members
419,553
Latest member
hanahass

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top