Hi I want to work out the following. For every hour somebody works at my company they earn 12.07% of holiday allowance and i don't know what formula to use.so some body works for 10 hours i know they are intitled to 1.21 hours of holiday but i don't know what sum to use please helpThe hous worked is in the HH MM format
Hello and welcome to Just Answer. My name is XXXXXX XXX I will be happy to assist you with your question
Try the following formula
=hours worked * .01207
correction on the above......I mistyped
=hours worked * 0.1207
So if the hours worked were 10, and they are multiplied by 12.07% .........it will show as 1.207, if you decrease the decimal on the answer........it will show as 1.21
Please let me know if you have any questions
ok let me try it
Okay,, I'll be standing by
No it doesn’t work, I have tried that and the problem is when I use the formula the answer it gives is 1:12:25 and it should show 1:21:00. I will re draft my question
Hi I want to work out the following sum. For every hour somebody works at my company they earn 12.07% of a hour toward their holiday allowance and I don't know what sum/formula to use.
I know that for example if some body works for 10 hour they are entitled to 1.21 hours of holiday time off.
At present I have a excel work sheet that works out total hours worked in a month, the cell this appears in is formatted to Custom [h]:mm:ss. What I want is the completed sum so I can cut and paste it into the relevant box so if cell A1 has total hours work in the month I want cell B1 to have the total amount of hours earned toward paid holiday leave.
If you work casually or irregular hours it may well be easiest to calculate the holiday entitlement that accrues (accumulates) as hours are worked. The holiday entitlement of 5.6 weeks is equivalent to 12.07 per cent of the hours you worked. The 12.07 per cent figure is:
5.6 weeks' holiday, divided by 46.4 weeks (being 52 weeks - 5.6 weeks) multiplied by 100 = 12.07 per cent
The 5.6 weeks have to be excluded from the calculation as you would not be present during the 5.6 weeks in order to accrue annual leave. For example, if you had worked 10 hours, you would be entitled to 72.6 minutes' paid holiday:
12.07 per cent x 10 hours = 1.21 hours = 72.6 minutes
The holiday entitlement is just over seven minutes for each hour worked.
Okay, give me some time and I will work on it. You say the hours worked are in a custom format correct?
Sorry Yes, under category i have used the Custom for mate [H]:mm:ss for all the data contain times
So if the hours worked are say 30 hours........how is it displayed in the cell?
30:00:00 and if they worked 12 hours 13 min 23 sec it would show as 12:13:23
Thank you for that information. Please give me some time to work out the kinks
Ok Should i stay on line?
You don't have to stay on line........when I reply you will be notified by email
one question though
whats your question
What is the formula for the hours worked.............or are you typing them in manually for testing
Is it just a sum of so many days
Not total sure what you mean. On my sheet i have a start time say 08:00:00 then a finish time say 16:30 the sheet then works out the total hours, in this case 8:30:00. There is a line for each day so at the end of the week it gives a total hours work in the week all this formats are in the [H]:mm:ss
I cn e-mail you the sheet to look at if you want
Okay, thank you for that information.
Sorry i Can
You can upload the file ...........just go to www.wikisend.com and choose upload..........then come back here and let me know the ID# XXXX it gives you after the upload. I know email would be easier, but it is against policy
If you can't that is okay
Just signing up now
no need to sign up
just choose "upload file"
Hi i have tried but it uploads the crashes!!!
Okay, not a problem. I will work on your issue..........I am on EST and getting a bit late. Is there a rush on this?
No Tomorrow is good for me it 3.47am here so best get to bed myself
You can also try http://sharesend.com/ to upload file........if you do let me know the link it gives afterwards. If you can't that's fine....
Oh my, very late for you
no need to upload then.....get some sleep
Do not mean to keep you up .....I know it is late for you. The worksheet does not need to be uploaded. I have enough information to go on
I will be standing by until you are out of chat.........in case you type something else, otherwise, sleep well and I will be in touch tomorrow with the results.
Okay, I believe I have it working like you want
Try the following:
Step 1: =hours worked * 24 * 0.1207 ignore the format of the result (step 2 will fix that)
Step 2: In the cell that contains the answer, go to format cells, and format it to a NUMBER
If hours worked are 10. then the result will be 1.21 (after formatting to a number)
If you have any questions or problems please let me know before rating my answer and I will continue to assist you. If satisfied, I would appreciate a good rating.
Did it work for you?
Hi, Sorry but the sum doesn't work. When you use it to calculate the 10 hours allowance its spot on but when you use it to calculate 1 hour its wrong. I don't know if we are coming at it the wrong way. The basic figures behind the sum are this
for every 1 hour you work you accrue 7minutes 26seconds of holiday. The sum needs to work out the following i have a box with the total hours worked for the sake of the sum lets say 1 hour (box format for total hours is [H]:mm:ss). The sum i am after needs to look at this box with the total hours in and add 00:07:62 second for every hour shown giving me a total amount of holiday entitlement earned. When you come back on line see what you can do and i will check in later.
I will see what I can do later on this evening with it. Thank you for the update
If you can upload file, that would be very helpful. If you can't, I understand and I will see what I can do with it
So, it needs to show 00:07:02 to each total hour worked?
or should I say add 00:07:02 to each hour worked
sheesh, typing getting the best of me today. I meant to type :00:07:62
Then 10 hours should show 72.6 minutes correct?
Yes, 10 hours should should 72.6 minutes of holiday
Just about there........will explain in the morning (my time)
Okay try the following steps.. (I am using cell A1 for the example of hours worked)1. Remove the formula that we put in prior2. Insert the following formula in (I'm using B1 for my example) =A1*24*0.121 3. That should result to 12.1 after converting it to a number in B14. Now in another cell input the following cell C1 for my example5. =CONVERT(B1,"hr","mn")6. That should result to 72.6 in C1 if the total hours worked are 10, and if you change the total hours worked to 1 it results to 7.26
Hope this helps
You will need to play around with the formatting to convert the 72.6 to minutes and seconds
Actually, I believe a formula is needed to convert the answer to a format of Hours, Minutes and Seconds
How did it go?
Microsoft Certified and 14 years teaching Microsoft Office to corporate and residential