Automatically subtract an amount each month from a cell

[DarkSoul]

I think the title sums this one up for the most part. I have a cell with a value in it, I just want to automatically subtract 150 from that value each month when the month changes.

What would be the best way to approach that?

[JohnTopley]

We still need a workbook (see yellow banner for instructions on how to attach) as we have no idea how your data is organised.

[kvsrinivasamurthy]

Workbook_Open event is used. When workbook s opened if month is different from the month of E2 amount is deducted. E2 will get Today() date.

Please Login or Register  to view this content.

Developer-->> visual Basic-->> VB window opens
select Workbook--> Paste the Code-->> Save the file as .xlsm

[JohnTopley]

Amended VBA re Range references

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Thanks John topley for correction.
Pl see file for corrected code.

[DarkSoul]

example included.... I want to automatically subtract number 2 from number 1 every month.

I thought this could be done with a formula though, or can it only be done with VBA?

[Glenn Kennedy]

It can be done with a formula:

=25400-DATEDIF(DATE(2023,3,1),TODAY(),"m")*B2

This will subtract the first 103.5 on 1st April...another one on 1st May, etc.

Test it using

=25400-DATEDIF(DATE(2023,3,1),DATE(2023,4,1),"m")*B2

where the bit in red is a future date (Y,m,d)

[DarkSoul]

Much appreciated.
All the formulas I've been shown in the last week or so have really helped make my budget spreadsheet much much easier to manage.