Excel formula to track accrued vacation [Solved]

Registration date
Wednesday December 12, 2018
Last seen
December 12, 2018

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 

1 reply

Best answer
Registration date
Sunday September 12, 2010
Last seen
October 15, 2019
Thank you
Hi Zonda,

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


Best regards,

Say "Thank you" 1

A few words of thanks would be greatly appreciated. Add comment

CCM 6348 users have said thank you to us this month

Respond to TrowaD