Posts1Registration dateWednesday December 12, 2018StatusMemberLast seenDecember 12, 2018
Updated on Dec 12, 2018 at 02:59 PM
Posts2921Registration dateSunday September 12, 2010StatusModeratorLast seenJanuary 16, 2023
Dec 13, 2018 at 11:10 AM
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