cumulated balance based on date 1

[Kevinski]

Hi

I have designed a spreadsheet to track employees various leave allowances (ie annual, sick, bereavement etc).

I have a sheet for each employee with a starting balance (ie the number of days leave due as at today) a work area where any days leave taken are recorded and finally a running balance showing how many days remain.

But I also have to account for a fresh dump of leave days each month (ie each employee accumulates about 1.7 days leave each month) and add that to the opening balance in an automated fashion. Any idea how I could do this?

All I can think of is some prompt for todays date on opening the spreadsheet and then have a cell formula which calculates how many days to add based on todays date minus the opening balance date - what would that formula look like?

Example
A - Opening balance 20 days
B - Accumulated leave 4 days
C - Leave taken 10 days
D - Current balance 14 days (ie A + B - C)

but how can I auto-calculate 'B'

I know at what monthly rate each employee receives a leave allowance.

Thanks

 

[DPlank]

Assuming the Opening balance date is in A1 then

=Today() - A1

will give you the current date minus the opening balance - this gives you the number of days passed. Use this to calc the additional accumulated leave and add it.

Subtract the leave taken and you're done.

Cheers,
Dave

Probably the only Test Analyst on Tek-Tips

animadverto vos in Abyssus!

Take a look at Forum1393 & sign up if you'd like