# Calculating future time off based on an entered date



## NKBingham (Dec 19, 2022)

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 (Dec 19, 2022)

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

Book1BCDEFGHIJ12023PTO Tracker2Today:1/8/20232/7/202334PTOAccrued to DateUsed to DateCurrent Balance58.6517.6567Anticipated RemainingFuture Scheduled Thru 2/7/23Balance824.95222.95910DateHoursPay Period EndingPay DateSum of Earned PTO by week111/6/202311/8/20231/13/20238.65122/6/202311/22/20231/27/202317.3133/30/202312/5/20232/10/202325.95142/19/20232/24/202334.6153/5/20233/10/202343.25163/19/20233/24/202351.9174/2/20234/7/202360.55Sheet3Cell FormulasRangeFormulaD5D5=SUMIF($B$11:$B$33,"<="&C2,$C$11:$C$33)E5,E8E5=C5-D5D7D7="Future Scheduled Thru " &TEXT(E2,"m/d/yy")C8C8=LOOKUP(E2,$H$11:$I$36,$J$11:$J$36)-D5D8D8=SUMIF($B$11:$B$33,">"&C2,$C$11:$C$33)J11J11=C5H12:I17H12=H11+14J12:J17J12=$C$5+J11


----------



## NKBingham (Dec 20, 2022)

breynolds0431 said:


> 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
> 
> ...


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


----------

