Excel formula to track accrued vacation [Solved]

Posts
1
Registration date
Wednesday December 12, 2018
Last seen
December 12, 2018
-
Hello,

I need help writing a formula in excel to track accrued vacation / personal time. During our employees first year of employment they earn PTO. Day 1 is earned on day 1. Day 2 is earned in 30 days. day 3 in 3 months etc.

Right now I have today's date entered in Cell T1 as "=now()" so the date is automatically calculated.

I have the dates they would earn the vacation already calculated. For example, if an employee started on 12/11/2018 (entered in cell N1) I have a cell with this formula linked to the 3rd day earned: =EDATE(N1,S6). This calculates 3 months of time added to cell N1. The result is 1/11/2019.

Okay so now I want to take a formula to calculate their earned hours as these dates pass.

For example =IF(T1>T4,U4). Where T1 is today's date, T4 is the date they earned their 1st day of PTO and U4 is the hours that are now earned. With this formula, Excel displays 8 hours in my cell. It works correctly.

Now, how do I say this =IF(T1>T4,U4) AND/OR =if(T1>T5,U5) Where T5 is the date they earned their 2nd day of PTO and U4 is the hours they now earned. If today's date is greater than T5 then it should display 16 hours of earned PTO.

I hope I explained this well enough for someone to help! I appreciate the input. This is a tricky one.



System Configuration: Windows / Chrome 70.0.3538.110
See more 

Your reply

1 reply

Best answer
Posts
2441
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 18, 2018
400
1
Thank you
Hi Zonda,

So basically you are asking how to nest to IF functions, right?

=IF(T1>T5,U5,IF(T1>T4,U4))

Best regards,
Trowa

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 4640 users have said thank you to us this month

Respond to TrowaD