Excel help.

[Ronnie R]

Hi all,

I'm just updating my personal spreadsheet where I track my incomings and outgoings.

I want my spreadsheet to deduct a cell £60 every 26th of the month. I have googled, but can't seem to make sense of the formluas.

Help will be appreicated.

Thanks

 

[Malky]

I have a formula I have checked and it seems to work, but depends how your spreadsheet is laid out.

 

[Ronnie R]

Post the formula and I shall change the cells if poss.

C31 is the cell I want deducted.

 

[Sym0n]

=IF(DAY(TODAY())=DAY(A1),B1-60,"")

Where A1 would be 26 and B1 would be the figure you want 60 deducting from...I think.

 

[nth]

=sum(c31-60)
put this in the cell where you would like to see the result of the 60 being deducted, change the C31 where necessary.

 

[Ronnie R]

=IF(DAY(TODAY())=DAY(A1),B1-60,"")

Where A1 would be 26 and B1 would be the figure you want 60 deducting from...I think.

You're so close! apart from A1 adds 60 rather than subtracts

 

[Sym0n]

Eh?

A1 would have 26 in it and B1 would be the total of your balance with 26 deducted from it.

Anyway you could actually do away with the cell call to A1 and use:

=IF(DAY(TODAY())=DAY(26),B1-60,"")

Obviously this is quick and dirty, you'd need a more complex formula or VBa to make this anyway useable on a balance sheet.

 

[Ronnie R]

I have this currently

=IF(DAY(TODAY())=DAY(19),C31-60)

Changed it to the 19th to see if it works. This formula adds 60.

If I do.. =IF(DAY(TODAY())=DAY(19),760-60)

All this does is take away 60 on the 19. It won't however do this every 19th, on any other day it shows false. Come the following 19th it will show as 700 again. Am I doing it wrong lol?

 

[Sym0n]

Are you using a cumulative balance spreadsheet instead of monthly? If so (why?), no the above won't work as once the date changes then it will undo the deduction. You'd need to go down the VBa route for that...and I've not got time to write it.

What figures in C31 though or do you have any weird formatting on the cell? I'm struggling to figure out why a deduction would become an addition and can reproduce the problem. :S

Personally I just have a monthly cashflow spreadsheet, which just deducts all my fixed monthly outgoings from my monthly pay. No point worrying about when it goes out, as it's spent already IMO.