How to repeat a formula and reset to zero on a monthly base?

[LRATOZ]

Hi,
I've got one big table in which I keep my weather data.
Every day I add a line to the table with new data.
So, at the start of a new month I do the following:
On the first of the month in Column H Cell 100 =0
On the second of the month in Column H Cell 101 =G101+H100
This formula is then copied in that column till the end of the month.
On the first of the next month the system repeats.

My data goes back over the last five years and I will need to backfill many colums over all these years.
Obviously I can do it by hand but this will take some serious time and will be very repetitive.

I have attached a screenshot which shows the end of a month and then the start of a new month.

Does somebody know hoe I could write a formula (Or a VBA code) that when I click on the first day of the month I can insert this formula?

Many thanks in advance!
Cheers,

Luke

 

[Toadstool]

Hi LRATOZ,

If your date is in column A then would this in column H work?

=IF(DAY(A100)=1,0,H99+G100)

 

[LRATOZ]

Hi Toadstool,
Thank you very much for your suggestion! It works!
Now, I realize, I should freshen up on my IF-statements.
I was looking for something much more complex, but this works fanatstic.
Thank you very much for your contribution and have a nice day!
Luke

 

[Toadstool]

Hi Toadstool,
Thank you very much for your suggestion! It works!
Now, I realize, I should freshen up on my IF-statements.
I was looking for something much more complex, but this works fanatstic.
Thank you very much for your contribution and have a nice day!
Luke

You're welcome and thanks for the feedback.