Good morning everyone!
I am trying to create a spreadsheet which allows my staff to record (and calculate) their time in leiu for overtime work. I am also calculating the accruals of these as the time in lieu has to be used up within 60 days for example so I want to have the spreadsheet apply it based on the number of days since accrual.
I have worked that part out but the next part (which is where i'm stuck) is, when leave is actually taken, I want it to be taken from the older accrual rather than the newer accrual. Is there a formulae for doing this?
To add further complication, in the case where there is not enough leave accrued in greater than 60 days (for example) for what is then taken, is there a way for it to apply some of the amount and then take the residual and apply it to the 30 - 60 days column? Sorry i know this sounds complicated, but below is what i'm trying to work out.
GREATER THAN 60 30 - 60 LESS THAN 30
4 hours 10 hours 6 hours
A person takes 7 hours of leave. I want it to take the 4 hours from the GREATER THAN 60 and then apply the remaining 3 hours to the 30 - 60 hours (to reduce that to 7 hours)
Am i clutching at straws or is there a way of doing this. Hope this makes sense!!!
I am trying to create a spreadsheet which allows my staff to record (and calculate) their time in leiu for overtime work. I am also calculating the accruals of these as the time in lieu has to be used up within 60 days for example so I want to have the spreadsheet apply it based on the number of days since accrual.
I have worked that part out but the next part (which is where i'm stuck) is, when leave is actually taken, I want it to be taken from the older accrual rather than the newer accrual. Is there a formulae for doing this?
To add further complication, in the case where there is not enough leave accrued in greater than 60 days (for example) for what is then taken, is there a way for it to apply some of the amount and then take the residual and apply it to the 30 - 60 days column? Sorry i know this sounds complicated, but below is what i'm trying to work out.
GREATER THAN 60 30 - 60 LESS THAN 30
4 hours 10 hours 6 hours
A person takes 7 hours of leave. I want it to take the 4 hours from the GREATER THAN 60 and then apply the remaining 3 hours to the 30 - 60 hours (to reduce that to 7 hours)
Am i clutching at straws or is there a way of doing this. Hope this makes sense!!!
