Calculating future time off based on an entered date

[NKBingham]

I'm trying to find a way that I can auto calculate how much PTO would be available at the date of one's latest date of PTO in column B based on a given date. For example; it's January 8, 2023 today and I've earned my first 8.65 hours of PTO. I've used 1 hour on the 6th, so I know I'll have 7.65 hours left and my sheet automatically calculates that and puts the info into cells D5 and E5 respectively. But I also need to be able to show how much PTO I'll have earned at a given future date (E2) minus all PTO I've taken or plan to take up until that specific date. To clarify, my sheet shows 1 hour on 2/6/23 and one on 3/30/23, but I want to know only information up to 2/7/23 for instance. It sounds like it should be easy, but I can't seem to wrap my head around what I need to do.

 

[breynolds0431]

Hi. I have some assumptions for some formulas that may help in the below.

Used to Date(cell C5): uses a sumif formula to count hours only if the associated date is less than or equal to date in cell C2

Anticipated Remaining (cell C8): uses a lookup formula based on the date in E2 and the data in columns H:J (beginning on row 10). Then removes the Used to Date amount from cell D5.

Future Scheduled (cell D8): uses similar sumif formula from cell C5, this time summing the hours with dates greater than the date in cell C2

Book1
	B	C	D	E	F	G	H	I	J
1	2023		PTO Tracker
2	Today:	1/8/2023		2/7/2023
3
4	PTO	Accrued to Date	Used to Date	Current Balance
5		8.65	1	7.65
6
7		Anticipated Remaining	Future Scheduled Thru 2/7/23	Balance
8		24.95	2	22.95
9
10	Date	Hours					Pay Period Ending	Pay Date	Sum of Earned PTO by week
11	1/6/2023	1					1/8/2023	1/13/2023	8.65
12	2/6/2023	1					1/22/2023	1/27/2023	17.3
13	3/30/2023	1					2/5/2023	2/10/2023	25.95
14							2/19/2023	2/24/2023	34.6
15							3/5/2023	3/10/2023	43.25
16							3/19/2023	3/24/2023	51.9
17							4/2/2023	4/7/2023	60.55
Sheet3

Cell Formulas
Range	Formula
D5	D5	=SUMIF($B$11:$B$33,"<="&C2,$C$11:$C$33)
E5,E8	E5	=C5-D5
D7	D7	="Future Scheduled Thru " &TEXT(E2,"m/d/yy")
C8	C8	=LOOKUP(E2,$H$11:$I$36,$J$11:$J$36)-D5
D8	D8	=SUMIF($B$11:$B$33,">"&C2,$C$11:$C$33)
J11	J11	=C5
H12:I17	H12	=H11+14
J12:J17	J12	=$C$5+J11

 

[NKBingham]

Hi. I have some assumptions for some formulas that may help in the below.

Used to Date(cell C5): uses a sumif formula to count hours only if the associated date is less than or equal to date in cell C2

Anticipated Remaining (cell C8): uses a lookup formula based on the date in E2 and the data in columns H:J (beginning on row 10). Then removes the Used to Date amount from cell D5.

Future Scheduled (cell D8): uses similar sumif formula from cell C5, this time summing the hours with dates greater than the date in cell C2

Book1
	B	C	D	E	F	G	H	I	J
1	2023		PTO Tracker
2	Today:	1/8/2023		2/7/2023
3
4	PTO	Accrued to Date	Used to Date	Current Balance
5		8.65	1	7.65
6
7		Anticipated Remaining	Future Scheduled Thru 2/7/23	Balance
8		24.95	2	22.95
9
10	Date	Hours					Pay Period Ending	Pay Date	Sum of Earned PTO by week
11	1/6/2023	1					1/8/2023	1/13/2023	8.65
12	2/6/2023	1					1/22/2023	1/27/2023	17.3
13	3/30/2023	1					2/5/2023	2/10/2023	25.95
14							2/19/2023	2/24/2023	34.6
15							3/5/2023	3/10/2023	43.25
16							3/19/2023	3/24/2023	51.9
17							4/2/2023	4/7/2023	60.55
Sheet3

Cell Formulas
Range	Formula
D5	D5	=SUMIF($B$11:$B$33,"<="&C2,$C$11:$C$33)
E5,E8	E5	=C5-D5
D7	D7	="Future Scheduled Thru " &TEXT(E2,"m/d/yy")
C8	C8	=LOOKUP(E2,$H$11:$I$36,$J$11:$J$36)-D5
D8	D8	=SUMIF($B$11:$B$33,">"&C2,$C$11:$C$33)
J11	J11	=C5
H12:I17	H12	=H11+14
J12:J17	J12	=$C$5+J11

This is perfect. Thank you so much for your help!