Time off in Lieu (TOIL) - Running balance with time expiry

JTM92

New Member
Joined
Dec 16, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
I'm currently trying to make a TOIL timesheet for employees. It has always been done on paper, manually. I am looking to use the same method but digitize and automate.
- We accrue 1.5 hours for ever hour worked over 39 hours.
- We manually type in hours worked along with the date worked. I'm not looking for a formula for automatically logging TOIL after 39 hours, as we log our TOIL manually in hours worked (B4).
- Any TOIL not used within 6 weeks of the "Date Worked" is lost.
- Available Balance changes with hours accrued and hours taken, as well as being affected by hours lost (not used within 6 weeks)
I am struggling in creating a formula that has a running balance (D1) taking into account Hours accrued (C3), and Hours Taken (D3) that incorporates the time limit of 6 weeks, uses Hours Accrued amount from earliest first before it is timed out, and removes any accrued hours not used up within that time.

Is what I want to achieve possible, or am I trying to over-complicate? Any suggestions would be greatly received. Please let me know if any information is unclear, or more is needed.

Here is a example of the form we use, including cell references, should anyone be able to give advice.

ABCDEFG
1Current Available Balance:0.5
2
3Dates worked:Hours Worked:Hours Accrued:Hours Taken:Reason:Agreed By:Date Agreed:
412/10/202046Met client early
518/12/202011.5Met client late
623/12/20201Extended lunch
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,499
Office Version
  1. 365
Platform
  1. Windows
Hi, does it work if you just sum the hours accrued that are that are not older than 6 weeks ago and deduct the sum of the hours taken that are also not older than 6 weeks ago?

Something like:

Book1
ABCD
1Current Available Balance:0.5
2
3Dates worked:Hours Worked:Hours Accrued:Hours Taken:
412/10/202046
518/12/202011.5
623/12/20201
Sheet1
Cell Formulas
RangeFormula
D1D1=SUMIFS(C4:C100,A4:A100,">"&TODAY()-(7*6))-SUMIFS(D4:D100,A4:A100,">"&TODAY()-(7*6))
 

JTM92

New Member
Joined
Dec 16, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
Hi, does it work if you just sum the hours accrued that are that are not older than 6 weeks ago and deduct the sum of the hours taken that are also not older than 6 weeks ago?

Something like:

Book1
ABCD
1Current Available Balance:0.5
2
3Dates worked:Hours Worked:Hours Accrued:Hours Taken:
412/10/202046
518/12/202011.5
623/12/20201
Sheet1
Cell Formulas
RangeFormula
D1D1=SUMIFS(C4:C100,A4:A100,">"&TODAY()-(7*6))-SUMIFS(D4:D100,A4:A100,">"&TODAY()-(7*6))

Thank you for getting back to me so quickly! This almost works, and has certainly set me on the right path...however the problem it faces is if you don't accrue any TOIL for a while, but then take some hours towards the end of the accrued's 6 weeks. You then end up with a negative for the period that the Hours earned has timed out, but the hours taken is still within a 6 week period. Any toil accrued in this period would then bring it back up from the -5 (until the hours taken times out).

Here is an example, as I'm aware I have struggled to explain.

Today16/12/2020

A
BCDEFG
1Current Available Balance:-5.00
2
3Dates worked:Hours Worked:Hours Accrued:Hours Taken:Reason:Agreed By:Date Agreed:
403/11/202057.5Met client early
512/12/20205Met client late
6Extended lunch

As you can see the accrued period has ended for the 7.5 (D4), but not for the 5 taken (E5)

I will toy with what you've sent over, but further guidance would be much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,730
Members
417,108
Latest member
Thein Than

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

Which adblocker are you using?

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
Top