Need help creating forumula for PTO accural

Hi Guys,

I am a little rusty with my excel formula's and I need to create a spreadsheet that shows the PTO accrual of employees.

Date of Hire C3
Current Date = D3
Years of Service = G3


PTO
1 year = 5 days
3 years = 10 days
6 years = 15 days
10 years = 20 days

I would appreciate any help with this!

Try...

Formula:

=IF(G3<1,0,IF(G3>=10,20,IF(G3>=6,15,IF(G3>=3,10,5))))

It seemed to work for the first one, but when I drug it down, it shows them all having 20 days of PTO. Any suggestions? Thank you for the help!

It should work fine, so I'm going to assume that your 'Years of Service' column is formatted as 'Text' rather than 'Number' or 'General'.

You can convert numbers stored has text several ways, see here.

Finally, I noticed that I pasted a version of the formula I didn't intend to. What I have posted is fine and will work perfectly well, but the below is slightly rejigged to have all the outcomes in descending order...

Formula:

=IF(A1>=10,20,IF(A1>=6,15,IF(A1>=3,10,IF(A1>=1,5,0))))

I have the years of service by formula which is : =DATEDIF(F3, G3,"y") & " years, " & DATEDIF(F3,G3,"ym") & " months"

F3 is date of hire and G3 is the current date using the =today() formula.

I'm wondering if that is why it is not working on my spreadsheet? I posted the last formula you provided and it gave me 5 rows of 20 years, then the rest show as zero's.

I obviously need to take a refresher excel course. Thank you so much for your help!!

Try this ...

=LOOKUP(DATEDIF(F3, G3,"y"),{0,1,3,6,10},{0,5,10,15,20})

Awesome!! Thank you all so much! This works. Now, I have 1 more question... I need to be able to give them data as to what they will be receiving in 2018, 2019, 2020, etc.

How would I go about figuring that?

G3 is the current date using the =today() formula.

Change =today()

To = DATE(2018,1,1) etc

You're the best. Thank you very much! I am all set.

You're welcome!

Originally Posted by AlliBlank

I have the years of service by formula which is : =DATEDIF(F3, G3,"y") & " years, " & DATEDIF(F3,G3,"ym") & " months"

I see you're all set now, but just FYI, by appending the string " years" Excel can no longer see the numeric result on it's own, so it was unable to perform the calculation correctly.