Trouble with formula for calculating PTO, use SUMIF or maybe SUMIFS not sure

[kae2011]

I know what I want to do I have gotten everything else I am just pulling a complete blank with the last PTO formula. I need help with a formula to show if the previous month pto is greater than carry over … take the difference and add to current month vacation
AL14= previous months PTO V5= New PTO AL16= Current month pto L5= Carry over AM16=where this formula will go

I am trying to do this in the PTO tracker sheet in the attached document, any help would be greatly appreciated!

[Limor_OP]

Hi,
sorry for the ignorance - but what is PTO
in addition - can you add manually the results you are expecting (in AM16 and down?)

[kae2011]

PTO = Paid time off or vacation time... I would prefer to have a formula calculate it so i dont have to manually figure out the difference for 30+ employees
it doesnt have to be a sumif or sumifs that was just kind of what i was thinking

[kae2011]

Hi,
sorry for the ignorance - but what is PTO
in addition - can you add manually the results you are expecting (in AM16 and down?)

PTO = Paid time off or vacation time... I would prefer to have a formula calculate it so i dont have to manually figure out the difference for 30+ employees
it doesnt have to be a sumif or sumifs that was just kind of what i was thinking

[Limor_OP]

of course you want a formula but in order to understand what you are trying to achieve - please input manually the results you are expecting to see. Your explanation seems very vague, and hard to follow, maybe a live result will clear the picture
In addition - where is the "current month vacation"?

what is the result you are expecting to see in AM16 for example? I see there is already a value there....is it incorrect?

[kae2011]

of course you want a formula but in order to understand what you are trying to achieve - please input manually the results you are expecting to see. Your explanation seems very vague, and hard to follow, maybe a live result will clear the picture
In addition - where is the "current month vacation"?

what is the result you are expecting to see in AM16 for example? I see there is already a value there....is it incorrect?

the formula I am trying to fix is going into cell AM16 I need that formula to show "if the previous month pto (cell AL14) is greater than carry over (cell L5) … take the difference (anything over 16) and add to current month vacation (cell AL16)"

how our vacation is tracked is that we can have up to 16 hours of vacation time that carries over from the previous year, but it has to be used in January only. cells L5, Q5, & V5 auto populate with the hours that each employee has for the year.

[Limor_OP]

So the answer in AM16 should be zero?
try this:
=IF(AL14>$L$5,AL14-$L$5+AL16,0)

[kae2011]

So the answer in AM16 should be zero?
try this:
=IF(AL14>$L$5,AL14-$L$5+AL16,0)

No its never going to be a 0 value unless they don't take any vacation hours. I'm sorry I'm making this so confusing the value I need for cell AM16 is going to show =AL16+AM14 so that it subtracts all the vacation hours that they took in the current month off their total balance but without the carry over hours.

So for an example employee 1 starts with 16 carry over hours 40 short notice hours and 80 PTO.
In the month of January they took 32 PTO hours AL14 would show -32 and AM14 would show 64.
Then in the month of February they only take 12 PTO hours so cell AL16 would show -12 and AM16 would then show a balance of 52 hours

Essentially those 16 carry over hours just expire if they don't use them, but if they take vacation i need to show that if Januarys PTO is greater than the carry over take the amount that its over and add to Februarys current PTO to get the overall balance, but if Januarys PTO is less than the carry over you wouldn't be taking any hours off the balance

I hope that makes sense

[AliGW]

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

[kae2011]

The best possible thing i could come up with is this formula and i just want to make sure there isnt something more simple or to make sure this doesn't have errors

=IF((AL14*-1)>L5,(((((AL14*-1)-L5)*-1)+AL16)+V5),(V5+AL16))