Hi, 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.

✔ Best Answer

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 17everytime 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

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?

can you please copy paste your formula here so i can try modify it?

How about of instead of the Day, use the Month, and multiply by 1.25 MIKE

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 :-)

Ask Your Question

Weekly Poll