How to auto insert money on a set calendar date in my spreadsheet ?

[Flourgrader]

Hi Everyone,

I have 2 questions that I hope someone can help me with?

I have just retired from work, and my pension is been paid

every 28 days.

I would like to be able to have Excel auto insert this amount in (money xxx.00) into my spreadsheet.

I also have another small payment made on the first day of every month, I would like have Excel auto insert this figure too.

Could you please keep the answers simple as I am an inexperienced user of Excel, but trying to learn!

Thank You for your help.

 

[Toadstool]

Hi Flourgrader,

To insert or delete rows or columns you'd need to use VBA. You may be able to simulate it using functions if your spreadsheet design is conducive but we'd really need to see your design before offering solutions.

 

[Flourgrader]

Hi Toadstool,
Thanks for your help,
I don’t want to add columns or rows or delete anything.
If you can imagine having a calendar JAN to DEC.
I have cell (A1) holding a value of £400.00 that’s for January
I want Excel to know when 28 days has passed and automatically insert another £400.00 in cell (B1) this would be for February,and continue this theme every 28 days until we had reached December

 

[Logit]

.
Formula in B2 and copied to L2 : =IF(A2=TODAY()-28,TODAY(),"")
.

 

[Flourgrader]

Thanks very much Logit , I appreciate your time.
I understand the Formula.
Can I use a simula formula to auto insert a value on the 1st date of each month ? eg: 1st Jan , 1Feb > December ?

 

[Logit]

.
Tested this here and it appears to work :

=IF(TODAY()=DATE(YEAR(TODAY()),MONTH(TODAY()),1),"$100","")

The value you want auto entered goes inside the quotation marks. This example is $100. That is followed by the comma and two quotation marks
with nothing inside. If it is the first day of the month the $100 will show, otherwise the cell will be empty.

 

[Logit]

Disable Formula Updates in Excel 2010

 

[Toadstool]

Hi Flourgrader,

Let me give you a slight alternative. Here's a column of incomes, monthly value and then the first of each month formatted to only display the month.

Is this the type of thing you want?

Flourgrader.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Income	Amount	Day of Month	January	February	March	April	May	June	July	August	September	October	November	December	YTD
2	Pension	$2,400	28	$2,400	$2,400	$2,400	$2,400	$2,400	$2,400	$2,400	$2,400					$21,628
3	Blackmail Income	$400	1	$400	$400	$400	$400	$400	$400	$400	$400					$3,601
4																$0
5				$2,800	$2,800	$2,800	$2,800	$2,800	$2,800	$2,800	$2,800	$0	$0	$0	$0	$22,400
Income

Cell Formulas
Range	Formula
E1:O1	E1	=EOMONTH(D1,0)+1
D2:O3	D2	=IF(TODAY()>=D$1+$C2-1,$B2,"")
P2:P5	P2	=SUM(B2:O2)
D5:O5	D5	=SUM(D2:D4)

 

[Flourgrader]

Thank You, Logit and Toadstool, I do appreciate your time and help.
I can crack on and finish my spreadsheet now!

 

[Flourgrader]

Hi Toadstool,
I think i went wrong somewhere in my spreadsheet it does not look like your!
I get an value error when entering =EOMONTH(D1,0)+1 into cell(E1)
I tried changing January to 1/1/2020 but still get the error.
Screenshot Uploaded.
Any ideas why it's not working?
Thanks