Excel formula to track accrued vacation [Solved/Closed]

Report
Posts
1
Registration date
Wednesday December 12, 2018
Status
Member
Last seen
December 12, 2018
-
Posts
2629
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 25, 2020
-
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 reply

Posts
2629
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 25, 2020
426
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
Thank you

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

CCM 3511 users have said thank you to us this month