• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

  • Vacation payout calculator.xlsx
    60.4 KB · Views: 7
zeeworld
Okay
... if it would show as with one decimal, but then more challenges... =120/26 is more ...
Wonder with this ...
 

Attachments

  • Vacation payout calculator.xlsx
    60.5 KB · Views: 10
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?
 
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?
 
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
 
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 ?

You wrote something about 'seniority'
... where has used that term in tab2?
... where is connection with tab1's calculation?
 
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 ?

You wrote something about 'seniority'
... 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
yrsnon exempt/hourly employeeexempt/salaried
0-180 hrs120 hrs
1-580 hrs120 hrs
6-10120 hrs120 hrs
11+160 hrs160 hrs
and based on that you get a certain of total hours beginning of the years which are total ours C3 in employee tab
 
Terms ... terms ... terms ...
Above, one misdatacolumn
... if I missed then You could modify it too.
 

Attachments

  • Vacation payout calculator.xlsx
    61 KB · Views: 8
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?
 
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.
 
S. Das
Did You read my reply?
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?
 
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.
69298
 

Attachments

  • Vacation payout calculator.xlsx
    60.4 KB · Views: 2
Last edited:
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.
 
vletm
Let me answer your questions.
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.
 
vletm
There should be some technical issue..... My full reply is like this.....
69310
Here, I am attaching the same file for you......
 

Attachments

  • Vacation payout calculator.xlsx
    60.5 KB · Views: 5
Back
Top