Dear djudur, I do not get an paid vacation at my job and you get only 1 day for every 5 years - we should both move to the country (or job) that gives the 20 days plus 1 extra for each 5 years (like in the video)!! --excelisfun
Dear 4642261, The formula in decimal form: =2410/60 A: 40.167 The formula in minutes and hours: =QUOTIENT(2410,60)&" hours "&MOD(2410,60)&" minutes" A: 40 hours 10 minutes QUOTIENT gives the integer part & MOD gives remainder Another time video: RUclipsrsLoveExcel#24: Add Time by Hour, Minute or Day --excelisfun
Dear djudur, I tried you formula (=roundup(years/5;0)*1) and could not get it to work. For example, if you work 12 years, you get the 20 + (12-5+1)*1 = 28 (The plus 1 is because in this case the 5 years worked immediately yields an extra vacation day). Your formula yields 12/5 rounded up is 3, then 3 + 20 = 23. How did you get it to work? Please tell us, because elegant is always good! --excelisfun
Dear flavio06 , In the above template, maybe this would work: =IF(E11>=2,10,IF(E11>=1,5,0)) I do not have any videos specifically on sick leave. --excelisfun
what if the increase of entitlement is different for each year? for example Less than 2 years = 12 days, 2 - 5 years = 14days etc? How do we handle this?
Do you have a tutorial on how to create a payroll spreadsheet that can track vacation hours used, earned, and rolled over? All determined by amount of years worked.
we have a need to calculate vacation based on anniversary date, and years worked. For instance 1st year = 1.54 hrs/pay period. 26 pay periods in a year equals a total of 40 hrs/year. 2nd year = 3.08 hrs/pay period for a total of 80 hrs/year. 3rd year@ 4.62/pay period 120hrs/yr and 4th year@ 6.15/pay period 160 hrs/year. I need to create something that will allow me to also deduct hours used. Our time clock is supposed to have this built in, but it is not calculation accurately.
This is really helpful, thank you, however I would need this formula to do one more thing. How could you add to this formula so that it stops adding extra days after you have reached a maximum of 5 additional days? Thanking you in advance for your help.
Typically vacation days are accrued each pay period. After 3 month probation, how do you adjust the vacation days taken with the corresponding pro rated vacation pay? Please advise.
Do you have something for time off or "comp" time? I'm an instructor in the Army and each day is 24 hours with no sleep. The following day is a "comp" day. Any idea how if somebody is scheduled on A1, it would automatically input "COMP" on B1 (hope that makes sense.) And also for total days off. We work extensively and a lot and try to balance the schedule. For example: if somebody is scheduled to work A1(represented by a numeric value: 1-1, 1-2,1-3,etc), then B1 is automatically a "comp" day (represented by COMP type in B1), and C1 is a day off (represented by leaving blank). The only days that we count for days off are the blocks that are left blank. I'm trying to figure out formula that will allow me to create a column at the end of the 10 day work schedule that will recognize those blank spots as a day off. Any advice other than just counting them?
How can I calculate part time employees who are accruing 40 hrs after 1 year (aniversary date), working an average of minimun 35 hrs/week or 1820 hrs/year.
Dear djudur,
I do not get an paid vacation at my job and you get only 1 day for every 5 years - we should both move to the country (or job) that gives the 20 days plus 1 extra for each 5 years (like in the video)!!
--excelisfun
Dear 4642261,
The formula in decimal form:
=2410/60
A: 40.167
The formula in minutes and hours:
=QUOTIENT(2410,60)&" hours "&MOD(2410,60)&" minutes"
A: 40 hours 10 minutes
QUOTIENT gives the integer part & MOD gives remainder
Another time video:
RUclipsrsLoveExcel#24: Add Time by Hour, Minute or Day
--excelisfun
Dear djudur,
I tried you formula (=roundup(years/5;0)*1) and could not get it to work. For example, if you work 12 years, you get the 20 + (12-5+1)*1 = 28 (The plus 1 is because in this case the 5 years worked immediately yields an extra vacation day). Your formula yields 12/5 rounded up is 3, then 3 + 20 = 23.
How did you get it to work? Please tell us, because elegant is always good!
--excelisfun
Dear flavio06 ,
In the above template, maybe this would work:
=IF(E11>=2,10,IF(E11>=1,5,0))
I do not have any videos specifically on sick leave.
--excelisfun
what if the increase of entitlement is different for each year? for example Less than 2 years = 12 days, 2 - 5 years = 14days etc?
How do we handle this?
Do you have a tutorial on how to create a payroll spreadsheet that can track vacation hours used, earned, and rolled over?
All determined by amount of years worked.
we have a need to calculate vacation based on anniversary date, and years worked. For instance 1st year = 1.54 hrs/pay period. 26 pay periods in a year equals a total of 40 hrs/year. 2nd year = 3.08 hrs/pay period for a total of 80 hrs/year. 3rd year@ 4.62/pay period 120hrs/yr and 4th year@ 6.15/pay period 160 hrs/year. I need to create something that will allow me to also deduct hours used. Our time clock is supposed to have this built in, but it is not calculation accurately.
This is really helpful, thank you, however I would need this formula to do one more thing. How could you add to this formula so that it stops adding extra days after you have reached a maximum of 5 additional days? Thanking you in advance for your help.
Did you ever find an answer to this? I’m needing the same thing
Typically vacation days are accrued each pay period. After 3 month probation, how do you adjust the vacation days taken with the corresponding pro rated vacation pay? Please advise.
Do you have something for time off or "comp" time? I'm an instructor in the Army and each day is 24 hours with no sleep. The following day is a "comp" day. Any idea how if somebody is scheduled on A1, it would automatically input "COMP" on B1 (hope that makes sense.) And also for total days off. We work extensively and a lot and try to balance the schedule. For example: if somebody is scheduled to work A1(represented by a numeric value: 1-1, 1-2,1-3,etc), then B1 is automatically a "comp" day (represented by COMP type in B1), and C1 is a day off (represented by leaving blank). The only days that we count for days off are the blocks that are left blank. I'm trying to figure out formula that will allow me to create a column at the end of the 10 day work schedule that will recognize those blank spots as a day off. Any advice other than just counting them?
Try posting question to THE best Excel question site;
mrexcel[dot]com/forum
How can I calculate per pay period and pro rate?
how can i calacualte no of vacation days as per dubai labor law?
How can I calculate part time employees who are accruing 40 hrs after 1 year (aniversary date), working an average of minimun 35 hrs/week or 1820 hrs/year.
Clemen Cardona did u got the answer for this question above.. Please let me know. my email address is ronak_t_vyas@yahoo.com
@sholpp1 , currently working 70+ hours a week, no time, try this alternative site (not affiliated with excelisfun):
mrexcel [dot] com/forum
yA LOST me