Excel formula to track accrued vacation

Solved/Closed
zonda216 Posts 1 Registration date Wednesday December 12, 2018 Status Member Last seen December 12, 2018 - Updated on Dec 12, 2018 at 02:59 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Dec 13, 2018 at 11:10 AM
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

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Dec 13, 2018 at 11:10 AM
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
1