Substracting from oldest date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Janelle S - 18 May 2008 03:44 GMT

I am hoping that someone will be able to help with this one - any help would
be greatly appreciated.
I am wanting to calculate balances of leave accrued and leave taken by each
staff member with the leave taken being substracted from the oldest date of
accrued within the previou 12 months. If no leave is taken within 12 months
of the date accrued, then this accrued leave is "surrendered" (lost) eg.
Name Date Hrs Balance
Staff 1 taken 01/05/2008 8 2
Staff 1 accrued 01/02/008 10 10
Staff 2 taken 01/01/2008 8 0
Staff 2 accrued 01/12/2007 3 8
Staff 2 accrued 01/11/2007 5 5
Staff 1 accrued 01/01/2007 8 surrendered

Reply to this Message

Prashant Runwal - 18 May 2008 11:39 GMT

One alternative is a little bit change in the data structure and use of
simple formula like sumif. Follow these steps

1) Keep yearwise data in different sheets
2) Columns will be
Name Date Leave
3) In name column write name of staff as usual, date should also be as
usual. Levae accrued should be entered in + and leave taken should be entered
in -
4) Assuming aboe data is in range a1:c100
5) Now create an additional table at (say) e1 as follows
Name of staff (cell e1) Leave balance (cell f1)
wrie names of all staff from e2 to e3... and so on
write following formula in cell f2 (second row below leave balance column
=sumif($a$1:$a$100,$e2,$c$1:$c$100). Copy this formula from f3 to last
staff and you will get the result. at each date.

> I am hoping that someone will be able to help with this one - any help would
> be greatly appreciated.
[quoted text clipped - 9 lines]
> Staff 2 accrued 01/11/2007 5 5
> Staff 1 accrued 01/01/2007 8 surrendered

Reply to this Message