Solved automatic monthly increments in excel

Microsoft Excel 2010 - complete product...
May 17, 2012 at 05:29:02
Specs: Windows 7

I am maintaining an annual leave pay schedule consisting of 100 people. I manually update the schedule each time an employee takes leaves. At the end of every month each employee accrues 1.25 days worth of leave and I now go and manually increase the balance by 1.25 days. My question is whether or not there is a way to add 1.25 days automatically to the balance at the start of a new month?

Thanks for your help.

See More: automatic monthly increments in excel

Report •

✔ Best Answer
May 17, 2012 at 08:01:56
How about of instead of the Day, use the Month, and multiply by 1.25


May 17, 2012 at 06:48:41
I have just thought of this very quickly, and therefore not sure if it will work, or if there are better options out there.

What you 'could' do is,

1) select a cell in which you can record todays date (eg A1)
2) type the formula =today()
this will put todays date in that cell eg 17/05/2012
4) right click and select 'format cells'
format a cell using 'Custom' format and in the 'Type' field enter dd
This will only show the DAY in that field now eg 17

everytime you open this workbook it will now update the field so that it will show the current day.

you can now use a formula to test if the value in that field is '1' or '01' and accordingly update the balance.

if you are using vba you can also achieve this, let me know if you are using vba and we can try and help further.

for example if you select cell

Report •

May 17, 2012 at 07:14:56
Hi. Thanks for your input.

This is kind of what was trying, but instead I used the "now" formula and then used a "day" formula to get the current day. I then ran an "if" statement which when the day was 1, would add 1.25 to my balance. This works on the first day of the month, but the only problem is when the day changes to 2 and so forth, the formula reverts back to previous balance. So the problem I was having was maintaining the new balance. Every angle I tried from that point on resulted in a circular reference.

Any other thoughts?

Report •

May 17, 2012 at 07:19:39
can you please copy paste your formula here so i can try modify it?

Report •

Related Solutions

May 17, 2012 at 08:01:56
✔ Best Answer
How about of instead of the Day, use the Month, and multiply by 1.25


Report •

May 18, 2012 at 01:45:55
Hi Mike,

That is what I ended up doing. It will work fine untill the end of the year at which point I will have to adjust the balances for the new year.

Thanks for your input guys! Much appreciated :-)

Report •

Ask Question