# Vacation payout hours and dollar amount

#### zeeworld

##### New Member
Help! I am new to all of this and need a formula to calculate the number of hours and vacation payout amount using the company seniority schedule, max roll over from prior year (40) hrs, used and unused hours.

I have explained what i have come up with an employees unused vacation payout and what other person got in my team in payroll. I know i am paying them double for some reason my calculation has been coming out wrong and i want to get this calculator right to be able to plug in the data from HRIS system and calculate it right. I have included policy and schedule as well.

Any help or guidance you could give me would be appreciated!

Thanks!

#### Attachments

• 60.4 KB Views: 7

#### vletm

##### Excel Ninja
zeeworld
... where comes value 59.8?

#### vletm

##### Excel Ninja
zeeworld
Okay
... if it would show as with one decimal, but then more challenges... =120/26 is more ...
Wonder with this ...

#### Attachments

• 60.5 KB Views: 10

#### zeeworld

##### New Member
yeah i saw that initially too i dont know
zeeworld
Okay
... if it would show as with one decimal, but then more challenges... =120/26 is more ...
Wonder with this ...
yeah i saw that too, i don't know why i cant get it to say 19.8 instead. did you see tab 2? is there a way we can determine their seniority and pull how many total hours they can automatically?

#### vletm

##### Excel Ninja
i don't know why i cant get it to say 19.8 instead.
Many things depend rules.
I would to use true values from the beginning until the end.
... and that way, the You could get Your 'correct' result too.

see tab 2?
Should I try to guess, what would Your terms mean?

#### zeeworld

##### New Member
i don't know why i cant get it to say 19.8 instead.
Many things depend rules.
I would to use true values from the beginning until the end.
... and that way, the You could get Your 'correct' result too.

see tab 2?
Should I try to guess, what would Your terms mean?
Yes please if you can redo the calculator

#### vletm

##### Excel Ninja
zeeworld
D i d Y o u c h e c k t h a t m y r e t u r n e d f i l e ?

... where has used that term in tab2?
... where is connection with tab1's calculation?

#### zeeworld

##### New Member
zeeworld
D i d Y o u c h e c k t h a t m y r e t u r n e d f i l e ?

... where has used that term in tab2?
... where is connection with tab1's calculation?
Y e s I D i d C h e c k Y o u r F i l e
the hire date determines the seniority with the company, John Doe has been with a company for 1 year and is a salaried employee I am asking if we can determine years of service by (hire date 6/19/2019) and then get total hours
 yrs non exempt/hourly employee exempt/salaried 0-1 80 hrs 120 hrs 1-5 80 hrs 120 hrs 6-10 120 hrs 120 hrs 11+ 160 hrs 160 hrs
and based on that you get a certain of total hours beginning of the years which are total ours C3 in employee tab

#### vletm

##### Excel Ninja
Terms ... terms ... terms ...
Above, one misdatacolumn
... if I missed then You could modify it too.

#### Attachments

• 61 KB Views: 8

#### S. Das

##### Active Member
Terms ... terms ... terms ...
Above, one misdatacolumn
... if I missed then You could modify it too.
vletm, I think there is an error in the calculation of Total hours in C4, as you are only considering for Non-exempt employees (G4:G7) and overlooking Exempt employees (H4:H7). In the given example Jhon Doe is an exempt employee.
I also have one doubt about the pay period (C5). your logic is (Current Month)*2+1, but I think @zeeworld has not mentioned anything like this or I missed any logic??

Aside @zeeworld, why are you not following the easy logic as per your payroll admin?
my payroll admin told me the calculation was wrong because what he did is take 120/26 (payperiod in a year) = 4.6, 4.6x13 (current payperiods) = 59.8, 59.8 - 40 hrs (used) = 19.8 pay out hrs, 19.8x21.63 (his hourly rate) =428.274 payout amount;
Also, you change the logic in every step and asking vletm to redo the calculation?

#### zeeworld

##### New Member
vletm, I think there is an error in the calculation of Total hours in C4, as you are only considering for Non-exempt employees (G4:G7) and overlooking Exempt employees (H4:H7). In the given example Jhon Doe is an exempt employee.
I also have one doubt about the pay period (C5). your logic is (Current Month)*2+1, but I think @zeeworld has not mentioned anything like this or I missed any logic??

Aside @zeeworld, why are you not following the easy logic as per your payroll admin?

Also, you change the logic in every step and asking vletm to redo the calculation?
My payroll admins calculations change for every employee. based on their carry over hrs, used and unused hours and my head just wants a calculator to be able to plug in and not depend on him incase he leaves. or isnot available. I need to keep in mind both the exempt and the non exempt employees and their time with the company. ill do more research on this.

#### vletm

##### Excel Ninja
S. Das
Terms, terms, terms &
above, one misdatacolumn
... if I missed then You could modify it too.

If there is two columns to get values, isn't it 50/50 possible to guess wanted? ... and I gave a hint - what to do, if I guessed wrong.
Your 'doubt' about C5 .. that would be one possible to solve that.
'Think' ... sorry, sometimes have to do something too.
I tried to give ideas that zeeworld could do what this needs.
You are using term easy ... what would that mean?
Did You give any easy logic answers?

#### S. Das

##### Active Member
zeeworld
Please check this... A little modification of the file shared by @vletm.

vletm
the payroll admin calculates the payment based on the used hour, not on the available hour and from the very first zeeworld trying to calculate based on unused hours.
my payroll admin told me the calculation was wrong because what he did is take 120/26 (payperiod in a year) = 4.6, 4.6x13 (current payperiods) = 59.8, 59.8 - 40 hrs (used) = 19.8 pay out hrs, 19.8x21.63 (his hourly rate) =428.274 payout amount;
In the above calculation process
• 120 (allotted hour) depends on the employee, which you have already solved using Lookup function and I have just modified this for Exempt and Non-Exempt.
• 26 (Pay period in a year) should be the same for all the employee
• 13 (Current Pay period) should be the same for all employee
• 40 Hrs (used Hours) depends on the employee.
• 21.63 (Pay rate) depends on the employee.

#### Attachments

• 60.4 KB Views: 2
Last edited:

#### vletm

##### Excel Ninja
S. Das
... as You wish ...
was that easy logic?
a) if someone wanted to use unused hours - then let someone use those
b) Employee Type ... was it in original form ... does someone mention it?
c) Why enter current year pay period? It would depend, when will do calculation.
Total Hour depends of pay period and The 1st Date At Work?

There should be a connection - or of course, it depends of rules.
d) 59.8 ... isn't that value; because 4.6 seems to be rounded value.

#### S. Das

##### Active Member
vletm
A) "if someone wanted to use unused hours" - then he should share the logic of calculation also. Sharing the logic of used hour and calculate using unused hour is not crazy?

B) "Employee Type ... was it in original form ... does someone mention it?"-- You are absolutely right, this was not in original form, but as per reply of zeeworld I think this should be in the form.
I need to keep in mind both the exempt and the non exempt employees and their time with the company.

C) "Why enter current year pay period? It would depend, when will do calculation. " --of course Yes. If you check the file shared by me then you will get your answer.
"Total Hour depends of pay period and The 1st Date At Work?" Sorry, I don't understand the question about the pay period, but the total hour should depend on The 1st Date At Work, as based on your time with the company the total hour value changes.

D) "59.8 ... isn't that value; because 4.6 seems to be rounded value."-- Yes 4.6 is a rounded value. I rounded it to one decimal place as per the calculation of payroll admin.

S. Das

#### S. Das

##### Active Member
vletm
There should be some technical issue..... My full reply is like this.....

Here, I am attaching the same file for you......

#### Attachments

• 60.5 KB Views: 5