# Formulae to calculate Time In Lieu based on 60 days, 30 days etc

#### graemekoz

##### Board Regular
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!!!

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### earthworm

##### Well-known Member
Give a small piece with example manually and highlight the answer that you require along with manual working

Replies
5
Views
146
Replies
1
Views
128
Replies
9
Views
317
Replies
4
Views
201
Replies
1
Views
326

Threads
1,148,280
Messages
5,745,826
Members
423,981
Latest member
ph1l

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back