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

[JTM92]

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.

	A	B	C	D	E	F	G
1			Current Available Balance:	0.5
2
3	Dates worked:	Hours Worked:	Hours Accrued:	Hours Taken:	Reason:	Agreed By:	Date Agreed:
4	12/10/2020	4	6		Met client early
5	18/12/2020	1	1.5		Met client late
6	23/12/2020			1	Extended lunch

 

[FormR]

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
	A	B	C	D
1			Current Available Balance:	0.5
2
3	Dates worked:	Hours Worked:	Hours Accrued:	Hours Taken:
4	12/10/2020	4	6
5	18/12/2020	1	1.5
6	23/12/2020			1
Sheet1

Cell Formulas
Range	Formula
D1	D1	=SUMIFS(C4:C100,A4:A100,">"&TODAY()-(7*6))-SUMIFS(D4:D100,A4:A100,">"&TODAY()-(7*6))

 

[JTM92]

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
	A	B	C	D
1			Current Available Balance:	0.5
2
3	Dates worked:	Hours Worked:	Hours Accrued:	Hours Taken:
4	12/10/2020	4	6
5	18/12/2020	1	1.5
6	23/12/2020			1
Sheet1

Cell Formulas
Range	Formula
D1	D1	=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.

	Today	16/12/2020
A	B	C	D	E	F	G
1			Current Available Balance:	-5.00
2
3	Dates worked:	Hours Worked:	Hours Accrued:	Hours Taken:	Reason:	Agreed By:	Date Agreed:
4	03/11/2020	5	7.5		Met client early
5	12/12/2020			5	Met client late
6					Extended 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.

 

[Thecatman]

This is the only post I can find that is tackling the same problem as me. Did you ever figure it out?

 

[myall_blues]

@Thecatman can you explain the negative hours issue a little better.

Consider the following example:

Book1
	H	I	J	K
1			Current Available Balance:	0
2
3	Dates worked:	Hours Worked:	Hours Accrued:	Hours Taken:
4	24/07/2024	4	6
5	30/07/2024	1	1.5
6	7/10/2024
Sheet1

Cell Formulas
Range	Formula
K1	K1	=SUMIFS(J4:J100,H4:H100,">"&TODAY()-(7*6))-SUMIFS(K4:K100,H4:H100,">"&TODAY()-(7*6))

The available balance shows as zero, which is correct as the last accrual was over 6 weeks ago. If I now take an hour in K6 against today (7/10/2024) the balance goes to -1, which seems correct as there was never an hour to take?