Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating Leave
Hi
I am trying to do annual leave calculation what i need is an automatic calculation for the following The rule is Every employee is entitled to a fully paid annual vacation after the completion of 3 months 1 to 5 years of consecutive service fifteen (15) days per year 5 to 10 years of consecutive service seventeen (17) days per year 10 to 15 years of consecutive service nineteen (19) days per year B C D 4 Name George 5 Staff Number 50004 6 Position 7 Starting Date 01-May-03 8 Total Working Days (NOW()-C7)/30/12 9 Annual Leave Days Taken Days Remaining 10 15 +D39 B11- C10 11 Total Days Allowed 12 =5*15+(C8-5)*17 39 From Day Inclusive To Day Exclusive Total Annual Leave A8 =The formula that i am using to find the Annual Leave pr year is the following =IF(TODAY()-C710*365,19,IF(TODAY()-C75*365,17,IF(TODAY()- C70.25*365,15))) What i want is an automatic calculation for the total days allowed instead of manually putting the formula i want it to deduct the previous 5 years as pr the rules =5*15+(C8-5)*17 What is the formula that allows me to do that ? what i need is an automatic formula to be updated alone on the B12 Thx in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating Leave
I am a tad confused by what you want to do.
You have the leave entitlement, what more is there to know? What is C8 calculating, and what is the relevance of this mysterious =5*15+(C8-5)*17 formula. -- __________________________________ HTH Bob "Tia" wrote in message ... Hi I am trying to do annual leave calculation what i need is an automatic calculation for the following The rule is Every employee is entitled to a fully paid annual vacation after the completion of 3 months 1 to 5 years of consecutive service fifteen (15) days per year 5 to 10 years of consecutive service seventeen (17) days per year 10 to 15 years of consecutive service nineteen (19) days per year B C D 4 Name George 5 Staff Number 50004 6 Position 7 Starting Date 01-May-03 8 Total Working Days (NOW()-C7)/30/12 9 Annual Leave Days Taken Days Remaining 10 15 +D39 B11- C10 11 Total Days Allowed 12 =5*15+(C8-5)*17 39 From Day Inclusive To Day Exclusive Total Annual Leave A8 =The formula that i am using to find the Annual Leave pr year is the following =IF(TODAY()-C710*365,19,IF(TODAY()-C75*365,17,IF(TODAY()- C70.25*365,15))) What i want is an automatic calculation for the total days allowed instead of manually putting the formula i want it to deduct the previous 5 years as pr the rules =5*15+(C8-5)*17 What is the formula that allows me to do that ? what i need is an automatic formula to be updated alone on the B12 Thx in advance |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating Leave
On Jul 24, 10:59*am, "Bob Phillips" wrote:
I am a tad confused by what you want to do. You have the leave entitlement, what more is there to know? What is C8 calculating, and what is the relevance of this mysterious =5*15+(C8-5)*17 formula. -- __________________________________ HTH Bob "Tia" wrote in message ... Hi I am trying to do annual leave calculation what i need is an automatic calculation for the following The rule is Every employee is entitled to a fully paid annual vacation after the completion of 3 months 1 to 5 years of consecutive service fifteen (15) days per year 5 to 10 years of consecutive service seventeen (17) days per year 10 to 15 years of consecutive service nineteen (19) days per year * * * * * B C D 4 * * * Name * * * * * * * * * * * * * *George 5 * * * Staff Number * * * * * * * * 50004 6 * * * Position 7 * * * Starting Date * * * * * * * * *01-May-03 8 * * * Total Working Days * * * (NOW()-C7)/30/12 9 * * * Annual Leave * * * * * * * * Days Taken * * * * * * * *Days Remaining 10 * * *15 +D39 * * * * * * * * * * * * * * * *B11- C10 11 * * *Total Days Allowed 12 * * *=5*15+(C8-5)*17 39 * * *From Day *Inclusive * * *To Day Exclusive * * * * *Total Annual Leave A8 =The formula that i am using to find the Annual Leave pr year is the following =IF(TODAY()-C710*365,19,IF(TODAY()-C75*365,17,IF(TODAY()- C70.25*365,15))) What i want is an automatic calculation for the total days allowed instead of manually putting the formula i want it to deduct the previous 5 years as pr the rules *=5*15+(C8-5)*17 What is the formula that allows me to do that ? what i need is an automatic formula to be updated alone on the B12 Thx in advance- Hide quoted text - - Show quoted text - I have the formula to give me based on the starting date the total of days pr year what i cannot automaticly calculate is : If you check the rules you will see the following Every employee has the right to keep his vacations for 2 years so whenever he finishes his 4 th year and and start his 5th all his vacation will be as 17 days wich what should happened is 15 days from the first year and 17 days for the second thats why i was using this formula =5*15+(C8-5)*17 wich means the first 5 years*15 days pr year +starting date-5 years that they are entitled in for 15 days as pr the rules and the rest of the time *17 days from 1 till 5 years = 15 days from 5 till 10 years = 17 days from 1o till 15 years = 19 days I am using this formula to give me how many days allowed pr year as pr today 15 or 17 or 19 what i need is a formula that deduct the yearswhenever i reach 5 years or 10 or 15 years automaticly from the starting date I hope i am being clear if i am not i can always send you my workbook Plz help Tia |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating Leave
Not really.
I think you are saying that they can accrue leave at the end of each year, so if they takes 7 of 17 days, the 10 is carried over and with the 17 for the new year, they now have 27. But you seem to want more, but I am not sure what. -- __________________________________ HTH Bob "Tia" wrote in message ... On Jul 24, 10:59 am, "Bob Phillips" wrote: I am a tad confused by what you want to do. You have the leave entitlement, what more is there to know? What is C8 calculating, and what is the relevance of this mysterious =5*15+(C8-5)*17 formula. -- __________________________________ HTH Bob "Tia" wrote in message ... Hi I am trying to do annual leave calculation what i need is an automatic calculation for the following The rule is Every employee is entitled to a fully paid annual vacation after the completion of 3 months 1 to 5 years of consecutive service fifteen (15) days per year 5 to 10 years of consecutive service seventeen (17) days per year 10 to 15 years of consecutive service nineteen (19) days per year B C D 4 Name George 5 Staff Number 50004 6 Position 7 Starting Date 01-May-03 8 Total Working Days (NOW()-C7)/30/12 9 Annual Leave Days Taken Days Remaining 10 15 +D39 B11- C10 11 Total Days Allowed 12 =5*15+(C8-5)*17 39 From Day Inclusive To Day Exclusive Total Annual Leave A8 =The formula that i am using to find the Annual Leave pr year is the following =IF(TODAY()-C710*365,19,IF(TODAY()-C75*365,17,IF(TODAY()- C70.25*365,15))) What i want is an automatic calculation for the total days allowed instead of manually putting the formula i want it to deduct the previous 5 years as pr the rules =5*15+(C8-5)*17 What is the formula that allows me to do that ? what i need is an automatic formula to be updated alone on the B12 Thx in advance- Hide quoted text - - Show quoted text - I have the formula to give me based on the starting date the total of days pr year what i cannot automaticly calculate is : If you check the rules you will see the following Every employee has the right to keep his vacations for 2 years so whenever he finishes his 4 th year and and start his 5th all his vacation will be as 17 days wich what should happened is 15 days from the first year and 17 days for the second thats why i was using this formula =5*15+(C8-5)*17 wich means the first 5 years*15 days pr year +starting date-5 years that they are entitled in for 15 days as pr the rules and the rest of the time *17 days from 1 till 5 years = 15 days from 5 till 10 years = 17 days from 1o till 15 years = 19 days I am using this formula to give me how many days allowed pr year as pr today 15 or 17 or 19 what i need is a formula that deduct the yearswhenever i reach 5 years or 10 or 15 years automaticly from the starting date I hope i am being clear if i am not i can always send you my workbook Plz help Tia |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating Leave
On Jul 24, 11:35*am, "Bob Phillips" wrote:
Not really. I think you are saying that they can accrue leave at the end of each year, so if they takes 7 of 17 days, the 10 is carried over and with the 17 for the new year, they now have 27. But *you seem to want more, but I am not sure what. -- __________________________________ HTH Bob "Tia" wrote in message ... On Jul 24, 10:59 am, "Bob Phillips" wrote: I am a tad confused by what you want to do. You have the leave entitlement, what more is there to know? What is C8 calculating, and what is the relevance of this mysterious =5*15+(C8-5)*17 formula. -- __________________________________ HTH Bob "Tia" wrote in message .... Hi I am trying to do annual leave calculation what i need is an automatic calculation for the following The rule is Every employee is entitled to a fully paid annual vacation after the completion of 3 months 1 to 5 years of consecutive service fifteen (15) days per year 5 to 10 years of consecutive service seventeen (17) days per year 10 to 15 years of consecutive service nineteen (19) days per year B C D 4 Name George 5 Staff Number 50004 6 Position 7 Starting Date 01-May-03 8 Total Working Days (NOW()-C7)/30/12 9 Annual Leave Days Taken Days Remaining 10 15 +D39 B11- C10 11 Total Days Allowed 12 =5*15+(C8-5)*17 39 From Day Inclusive To Day Exclusive Total Annual Leave A8 =The formula that i am using to find the Annual Leave pr year is the following =IF(TODAY()-C710*365,19,IF(TODAY()-C75*365,17,IF(TODAY()- C70.25*365,15))) What i want is an automatic calculation for the total days allowed instead of manually putting the formula i want it to deduct the previous 5 years as pr the rules =5*15+(C8-5)*17 What is the formula that allows me to do that ? what i need is an automatic formula to be updated alone on the B12 Thx in advance- Hide quoted text - - Show quoted text - I have the formula to give me based on the starting date the total of days pr year what i cannot automaticly calculate is : If you check the rules you will see the following Every employee has the right to keep his vacations for 2 years so whenever he finishes his 4 th year and and start his 5th all his vacation will be as 17 days wich what should happened is 15 days from the first year and 17 days for the second thats why i was using this formula =5*15+(C8-5)*17 wich means the first 5 years*15 days pr year +starting date-5 years that they are entitled in for 15 days as pr the rules and the rest of the time *17 days from 1 till 5 years = 15 days from 5 till 10 years = 17 days from 1o till 15 years = 19 days I am using this formula to give me how many days allowed pr year as pr today 15 or 17 or 19 what i need is a formula that deduct the yearswhenever i reach 5 years or 10 or 15 years automaticly from the starting date I hope i am being clear if i am not i can always send you my workbook Plz help Tia- Hide quoted text - - Show quoted text - Yes exactly But the issue is that the formula is calculating entitled years wich is 15 or 17 or 19 * starting date what i want is to calculate 15 days for the first 5 years and 17 days for the 2snd 5 years example : B4= Name C4= x B5=Staff Number C5=500 B6= Position C6=hr B7= Starting Date C7=5/1/2003 B8= Total Working Days C8= 5.31 YEARS =+(NOW()-C7)/30/12 B9= Annual Leave B10=TOTAL ENTITLED LEAVE =IF(TODAY()-C710*365,19,IF(TODAY()- C75*365,17,IF(TODAY()-C70.25*365,15)))=17 B11=TOTAL DAYS TILL THIS DAY ????????????????? I usually use the following formula =+C8*B10 if i use it i will have this total days =90.26 but whenever the employee reaches the 5 th years the formula will calculate wrong so i manually enter the following formula =5*15+(C8-5)*17 =80.27 what i need is an automatic formula that deduct them automaticly without manually entering the formula when the employee reached his 5th,1o th or 15 th years of services I hope that i made what i want clear this time Tia |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating Leave
How about this to calculate the total leave entitlement to-date
=IF((TODAY()-C7)365/4, ((ROUNDUP((TODAY()-C7)/365,0)5)*15*ROUNDUP((TODAY()-C7)/365,0)) +((ROUNDUP((TODAY()-C7)/365,0)10)+(ROUNDUP((TODAY()-C7)/365,0))-5)*2,0) -- __________________________________ HTH Bob "Tia" wrote in message ... On Jul 24, 11:35 am, "Bob Phillips" wrote: Not really. I think you are saying that they can accrue leave at the end of each year, so if they takes 7 of 17 days, the 10 is carried over and with the 17 for the new year, they now have 27. But you seem to want more, but I am not sure what. -- __________________________________ HTH Bob "Tia" wrote in message ... On Jul 24, 10:59 am, "Bob Phillips" wrote: I am a tad confused by what you want to do. You have the leave entitlement, what more is there to know? What is C8 calculating, and what is the relevance of this mysterious =5*15+(C8-5)*17 formula. -- __________________________________ HTH Bob "Tia" wrote in message ... Hi I am trying to do annual leave calculation what i need is an automatic calculation for the following The rule is Every employee is entitled to a fully paid annual vacation after the completion of 3 months 1 to 5 years of consecutive service fifteen (15) days per year 5 to 10 years of consecutive service seventeen (17) days per year 10 to 15 years of consecutive service nineteen (19) days per year B C D 4 Name George 5 Staff Number 50004 6 Position 7 Starting Date 01-May-03 8 Total Working Days (NOW()-C7)/30/12 9 Annual Leave Days Taken Days Remaining 10 15 +D39 B11- C10 11 Total Days Allowed 12 =5*15+(C8-5)*17 39 From Day Inclusive To Day Exclusive Total Annual Leave A8 =The formula that i am using to find the Annual Leave pr year is the following =IF(TODAY()-C710*365,19,IF(TODAY()-C75*365,17,IF(TODAY()- C70.25*365,15))) What i want is an automatic calculation for the total days allowed instead of manually putting the formula i want it to deduct the previous 5 years as pr the rules =5*15+(C8-5)*17 What is the formula that allows me to do that ? what i need is an automatic formula to be updated alone on the B12 Thx in advance- Hide quoted text - - Show quoted text - I have the formula to give me based on the starting date the total of days pr year what i cannot automaticly calculate is : If you check the rules you will see the following Every employee has the right to keep his vacations for 2 years so whenever he finishes his 4 th year and and start his 5th all his vacation will be as 17 days wich what should happened is 15 days from the first year and 17 days for the second thats why i was using this formula =5*15+(C8-5)*17 wich means the first 5 years*15 days pr year +starting date-5 years that they are entitled in for 15 days as pr the rules and the rest of the time *17 days from 1 till 5 years = 15 days from 5 till 10 years = 17 days from 1o till 15 years = 19 days I am using this formula to give me how many days allowed pr year as pr today 15 or 17 or 19 what i need is a formula that deduct the yearswhenever i reach 5 years or 10 or 15 years automaticly from the starting date I hope i am being clear if i am not i can always send you my workbook Plz help Tia- Hide quoted text - - Show quoted text - Yes exactly But the issue is that the formula is calculating entitled years wich is 15 or 17 or 19 * starting date what i want is to calculate 15 days for the first 5 years and 17 days for the 2snd 5 years example : B4= Name C4= x B5=Staff Number C5=500 B6= Position C6=hr B7= Starting Date C7=5/1/2003 B8= Total Working Days C8= 5.31 YEARS =+(NOW()-C7)/30/12 B9= Annual Leave B10=TOTAL ENTITLED LEAVE =IF(TODAY()-C710*365,19,IF(TODAY()- C75*365,17,IF(TODAY()-C70.25*365,15)))=17 B11=TOTAL DAYS TILL THIS DAY ????????????????? I usually use the following formula =+C8*B10 if i use it i will have this total days =90.26 but whenever the employee reaches the 5 th years the formula will calculate wrong so i manually enter the following formula =5*15+(C8-5)*17 =80.27 what i need is an automatic formula that deduct them automaticly without manually entering the formula when the employee reached his 5th,1o th or 15 th years of services I hope that i made what i want clear this time Tia |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating Leave
Correction
=IF((TODAY()-C7)365/4, ((ROUNDUP((TODAY()-C7)/365,0)0)*15*ROUNDUP((TODAY()-C7)/365,0)) +((ROUNDUP((TODAY()-C7)/365,0)5)*(ROUNDUP((TODAY()-C7)/365,0)-5)*2) +((ROUNDUP((TODAY()-C7)/365,0)10)*(ROUNDUP((TODAY()-C7)/365,0)-10)*2),0) -- __________________________________ HTH Bob "Tia" wrote in message ... Hi I am trying to do annual leave calculation what i need is an automatic calculation for the following The rule is Every employee is entitled to a fully paid annual vacation after the completion of 3 months 1 to 5 years of consecutive service fifteen (15) days per year 5 to 10 years of consecutive service seventeen (17) days per year 10 to 15 years of consecutive service nineteen (19) days per year B C D 4 Name George 5 Staff Number 50004 6 Position 7 Starting Date 01-May-03 8 Total Working Days (NOW()-C7)/30/12 9 Annual Leave Days Taken Days Remaining 10 15 +D39 B11- C10 11 Total Days Allowed 12 =5*15+(C8-5)*17 39 From Day Inclusive To Day Exclusive Total Annual Leave A8 =The formula that i am using to find the Annual Leave pr year is the following =IF(TODAY()-C710*365,19,IF(TODAY()-C75*365,17,IF(TODAY()- C70.25*365,15))) What i want is an automatic calculation for the total days allowed instead of manually putting the formula i want it to deduct the previous 5 years as pr the rules =5*15+(C8-5)*17 What is the formula that allows me to do that ? what i need is an automatic formula to be updated alone on the B12 Thx in advance |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating Leave
Actually that can be a lot simpler
=IF((TODAY()-C7)365/4, (ROUNDUP((TODAY()-C7)/365,0)*15) +((MAX(0,ROUNDUP((TODAY()-C7)/365,0)-5))*2) +((MAX(0,ROUNDUP((TODAY()-C7)/365,0)-10))*2),0) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... Correction =IF((TODAY()-C7)365/4, ((ROUNDUP((TODAY()-C7)/365,0)0)*15*ROUNDUP((TODAY()-C7)/365,0)) +((ROUNDUP((TODAY()-C7)/365,0)5)*(ROUNDUP((TODAY()-C7)/365,0)-5)*2) +((ROUNDUP((TODAY()-C7)/365,0)10)*(ROUNDUP((TODAY()-C7)/365,0)-10)*2),0) -- __________________________________ HTH Bob "Tia" wrote in message ... Hi I am trying to do annual leave calculation what i need is an automatic calculation for the following The rule is Every employee is entitled to a fully paid annual vacation after the completion of 3 months 1 to 5 years of consecutive service fifteen (15) days per year 5 to 10 years of consecutive service seventeen (17) days per year 10 to 15 years of consecutive service nineteen (19) days per year B C D 4 Name George 5 Staff Number 50004 6 Position 7 Starting Date 01-May-03 8 Total Working Days (NOW()-C7)/30/12 9 Annual Leave Days Taken Days Remaining 10 15 +D39 B11- C10 11 Total Days Allowed 12 =5*15+(C8-5)*17 39 From Day Inclusive To Day Exclusive Total Annual Leave A8 =The formula that i am using to find the Annual Leave pr year is the following =IF(TODAY()-C710*365,19,IF(TODAY()-C75*365,17,IF(TODAY()- C70.25*365,15))) What i want is an automatic calculation for the total days allowed instead of manually putting the formula i want it to deduct the previous 5 years as pr the rules =5*15+(C8-5)*17 What is the formula that allows me to do that ? what i need is an automatic formula to be updated alone on the B12 Thx in advance |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating Leave
On Jul 24, 2:16*pm, "Bob Phillips" wrote:
Actually that can be a lot simpler =IF((TODAY()-C7)365/4, * * * (ROUNDUP((TODAY()-C7)/365,0)*15) * * +((MAX(0,ROUNDUP((TODAY()-C7)/365,0)-5))*2) * * +((MAX(0,ROUNDUP((TODAY()-C7)/365,0)-10))*2),0) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... Correction =IF((TODAY()-C7)365/4, * * *((ROUNDUP((TODAY()-C7)/365,0)0)*15*ROUNDUP((TODAY()-C7)/365,0)) * *+((ROUNDUP((TODAY()-C7)/365,0)5)*(ROUNDUP((TODAY()-C7)/365,0)-5)*2) +((ROUNDUP((TODAY()-C7)/365,0)10)*(ROUNDUP((TODAY()-C7)/365,0)-10)*2),0) -- __________________________________ HTH Bob "Tia" wrote in message .... Hi I am trying to do annual leave calculation what i need is an automatic calculation for the following The rule is Every employee is entitled to a fully paid annual vacation after the completion of 3 months 1 to 5 years of consecutive service fifteen (15) days per year 5 to 10 years of consecutive service seventeen (17) days per year 10 to 15 years of consecutive service nineteen (19) days per year * * * * * B C D 4 * * * Name * * * * * * * * * * * * * *George 5 * * * Staff Number * * * * * * * * 50004 6 * * * Position 7 * * * Starting Date * * * * * * * * *01-May-03 8 * * * Total Working Days * * * (NOW()-C7)/30/12 9 * * * Annual Leave * * * * * * * * Days Taken * * * * * * * *Days Remaining 10 * * *15 +D39 * * * * * * * * * * * * * * * *B11- C10 11 * * *Total Days Allowed 12 * * *=5*15+(C8-5)*17 39 * * *From Day *Inclusive * * *To Day Exclusive * * * * *Total Annual Leave A8 =The formula that i am using to find the Annual Leave pr year is the following =IF(TODAY()-C710*365,19,IF(TODAY()-C75*365,17,IF(TODAY()- C70.25*365,15))) What i want is an automatic calculation for the total days allowed instead of manually putting the formula i want it to deduct the previous 5 years as pr the rules *=5*15+(C8-5)*17 What is the formula that allows me to do that ? what i need is an automatic formula to be updated alone on the B12 Thx in advance- Hide quoted text - - Show quoted text - When you have 15 years of service you should get as a leave 15*5 for the first 5 years 17*5 for the 2nd 5 years 19*5 for the 3rd 5 years so i used this formula cause the first 5 years he should have 15 days only the second five he should get 17 im really to sorry to bother but the total should be 80.27 S/d is C7=05/01/2003 C8==+(NOW()-C7)/30/12=5.31 cause =5*15+(C8-5)*17 __________=5*15+(5.31-5)*17=80.27 But when i use your formula i get =IF((TODAY()-C7)365/4,(ROUNDUP((TODAY()-C7)/365,0)*15)+ ((MAX(0,ROUNDUP((TODAY()-C7)/365,0)-5))*2)+((MAX(0,ROUNDUP((TODAY()- C7)/365,0)-10))*2),0)= 92 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating Leave
Another variation, part years now!
I can get closer, but not exactly what you get because you are using two different yardsticks. This formula =IF(TODAY()-C710*365,19,IF(TODAY()-C75*365,17,IF(TODAY()-C70.25*365,15))) is using a 365 day year, not totally accurate, but not far, but this one =(NOW()-C7)/30/12 uses a 360 day year, which is a huge 1.37% off. Using a consistent 365 day formula I get 78.53 for 5th May 2003. What do you want to do? -- __________________________________ HTH Bob "Tia" wrote in message ... On Jul 24, 2:16 pm, "Bob Phillips" wrote: Actually that can be a lot simpler =IF((TODAY()-C7)365/4, (ROUNDUP((TODAY()-C7)/365,0)*15) +((MAX(0,ROUNDUP((TODAY()-C7)/365,0)-5))*2) +((MAX(0,ROUNDUP((TODAY()-C7)/365,0)-10))*2),0) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... Correction =IF((TODAY()-C7)365/4, ((ROUNDUP((TODAY()-C7)/365,0)0)*15*ROUNDUP((TODAY()-C7)/365,0)) +((ROUNDUP((TODAY()-C7)/365,0)5)*(ROUNDUP((TODAY()-C7)/365,0)-5)*2) +((ROUNDUP((TODAY()-C7)/365,0)10)*(ROUNDUP((TODAY()-C7)/365,0)-10)*2),0) -- __________________________________ HTH Bob "Tia" wrote in message ... Hi I am trying to do annual leave calculation what i need is an automatic calculation for the following The rule is Every employee is entitled to a fully paid annual vacation after the completion of 3 months 1 to 5 years of consecutive service fifteen (15) days per year 5 to 10 years of consecutive service seventeen (17) days per year 10 to 15 years of consecutive service nineteen (19) days per year B C D 4 Name George 5 Staff Number 50004 6 Position 7 Starting Date 01-May-03 8 Total Working Days (NOW()-C7)/30/12 9 Annual Leave Days Taken Days Remaining 10 15 +D39 B11- C10 11 Total Days Allowed 12 =5*15+(C8-5)*17 39 From Day Inclusive To Day Exclusive Total Annual Leave A8 =The formula that i am using to find the Annual Leave pr year is the following =IF(TODAY()-C710*365,19,IF(TODAY()-C75*365,17,IF(TODAY()- C70.25*365,15))) What i want is an automatic calculation for the total days allowed instead of manually putting the formula i want it to deduct the previous 5 years as pr the rules =5*15+(C8-5)*17 What is the formula that allows me to do that ? what i need is an automatic formula to be updated alone on the B12 Thx in advance- Hide quoted text - - Show quoted text - When you have 15 years of service you should get as a leave 15*5 for the first 5 years 17*5 for the 2nd 5 years 19*5 for the 3rd 5 years so i used this formula cause the first 5 years he should have 15 days only the second five he should get 17 im really to sorry to bother but the total should be 80.27 S/d is C7=05/01/2003 C8==+(NOW()-C7)/30/12=5.31 cause =5*15+(C8-5)*17 __________=5*15+(5.31-5)*17=80.27 But when i use your formula i get =IF((TODAY()-C7)365/4,(ROUNDUP((TODAY()-C7)/365,0)*15)+ ((MAX(0,ROUNDUP((TODAY()-C7)/365,0)-5))*2)+((MAX(0,ROUNDUP((TODAY()- C7)/365,0)-10))*2),0)= 92 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating Leave
BTW, does the 5th year only count as 15 days or 17 days? Similarly, Is the
10th 17 or 19? So for example, is someone joining on 24th July 2002 entitled to 92 days or 94? -- __________________________________ HTH Bob "Bob Phillips" wrote in message ... Another variation, part years now! I can get closer, but not exactly what you get because you are using two different yardsticks. This formula =IF(TODAY()-C710*365,19,IF(TODAY()-C75*365,17,IF(TODAY()-C70.25*365,15))) is using a 365 day year, not totally accurate, but not far, but this one =(NOW()-C7)/30/12 uses a 360 day year, which is a huge 1.37% off. Using a consistent 365 day formula I get 78.53 for 5th May 2003. What do you want to do? -- __________________________________ HTH Bob "Tia" wrote in message ... On Jul 24, 2:16 pm, "Bob Phillips" wrote: Actually that can be a lot simpler =IF((TODAY()-C7)365/4, (ROUNDUP((TODAY()-C7)/365,0)*15) +((MAX(0,ROUNDUP((TODAY()-C7)/365,0)-5))*2) +((MAX(0,ROUNDUP((TODAY()-C7)/365,0)-10))*2),0) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... Correction =IF((TODAY()-C7)365/4, ((ROUNDUP((TODAY()-C7)/365,0)0)*15*ROUNDUP((TODAY()-C7)/365,0)) +((ROUNDUP((TODAY()-C7)/365,0)5)*(ROUNDUP((TODAY()-C7)/365,0)-5)*2) +((ROUNDUP((TODAY()-C7)/365,0)10)*(ROUNDUP((TODAY()-C7)/365,0)-10)*2),0) -- __________________________________ HTH Bob "Tia" wrote in message ... Hi I am trying to do annual leave calculation what i need is an automatic calculation for the following The rule is Every employee is entitled to a fully paid annual vacation after the completion of 3 months 1 to 5 years of consecutive service fifteen (15) days per year 5 to 10 years of consecutive service seventeen (17) days per year 10 to 15 years of consecutive service nineteen (19) days per year B C D 4 Name George 5 Staff Number 50004 6 Position 7 Starting Date 01-May-03 8 Total Working Days (NOW()-C7)/30/12 9 Annual Leave Days Taken Days Remaining 10 15 +D39 B11- C10 11 Total Days Allowed 12 =5*15+(C8-5)*17 39 From Day Inclusive To Day Exclusive Total Annual Leave A8 =The formula that i am using to find the Annual Leave pr year is the following =IF(TODAY()-C710*365,19,IF(TODAY()-C75*365,17,IF(TODAY()- C70.25*365,15))) What i want is an automatic calculation for the total days allowed instead of manually putting the formula i want it to deduct the previous 5 years as pr the rules =5*15+(C8-5)*17 What is the formula that allows me to do that ? what i need is an automatic formula to be updated alone on the B12 Thx in advance- Hide quoted text - - Show quoted text - When you have 15 years of service you should get as a leave 15*5 for the first 5 years 17*5 for the 2nd 5 years 19*5 for the 3rd 5 years so i used this formula cause the first 5 years he should have 15 days only the second five he should get 17 im really to sorry to bother but the total should be 80.27 S/d is C7=05/01/2003 C8==+(NOW()-C7)/30/12=5.31 cause =5*15+(C8-5)*17 __________=5*15+(5.31-5)*17=80.27 But when i use your formula i get =IF((TODAY()-C7)365/4,(ROUNDUP((TODAY()-C7)/365,0)*15)+ ((MAX(0,ROUNDUP((TODAY()-C7)/365,0)-5))*2)+((MAX(0,ROUNDUP((TODAY()- C7)/365,0)-10))*2),0)= 92 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating Leave
On Jul 24, 8:21*pm, "Bob Phillips" wrote:
BTW, does the 5th year only count as 15 days or 17 days? Similarly, Is the 10th 17 or 19? So for example, is someone joining on 24th July 2002 entitled to 92 days or 94? -- __________________________________ HTH Bob "Bob Phillips" wrote in message ... Another variation, part years now! I can get closer, but not exactly what you get because you are using two different yardsticks. This formula =IF(TODAY()-C710*365,19,IF(TODAY()-C75*365,17,IF(TODAY()-C70.25*365,15))*) is using a 365 day year, not totally accurate, but not far, but this one =(NOW()-C7)/30/12 uses a 360 day year, which is a huge 1.37% off. *Using a consistent 365 day formula I get 78.53 for 5th May 2003. What do you want to do? -- __________________________________ HTH Bob "Tia" wrote in message .... On Jul 24, 2:16 pm, "Bob Phillips" wrote: Actually that can be a lot simpler =IF((TODAY()-C7)365/4, (ROUNDUP((TODAY()-C7)/365,0)*15) +((MAX(0,ROUNDUP((TODAY()-C7)/365,0)-5))*2) +((MAX(0,ROUNDUP((TODAY()-C7)/365,0)-10))*2),0) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... Correction =IF((TODAY()-C7)365/4, ((ROUNDUP((TODAY()-C7)/365,0)0)*15*ROUNDUP((TODAY()-C7)/365,0)) +((ROUNDUP((TODAY()-C7)/365,0)5)*(ROUNDUP((TODAY()-C7)/365,0)-5)*2) +((ROUNDUP((TODAY()-C7)/365,0)10)*(ROUNDUP((TODAY()-C7)/365,0)-10)*2),0) -- __________________________________ HTH Bob "Tia" wrote in message ... Hi I am trying to do annual leave calculation what i need is an automatic calculation for the following The rule is Every employee is entitled to a fully paid annual vacation after the completion of 3 months 1 to 5 years of consecutive service fifteen (15) days per year 5 to 10 years of consecutive service seventeen (17) days per year 10 to 15 years of consecutive service nineteen (19) days per year B C D 4 Name George 5 Staff Number 50004 6 Position 7 Starting Date 01-May-03 8 Total Working Days (NOW()-C7)/30/12 9 Annual Leave Days Taken Days Remaining 10 15 +D39 B11- C10 11 Total Days Allowed 12 =5*15+(C8-5)*17 39 From Day Inclusive To Day Exclusive Total Annual Leave A8 =The formula that i am using to find the Annual Leave pr year is the following =IF(TODAY()-C710*365,19,IF(TODAY()-C75*365,17,IF(TODAY()- C70.25*365,15))) What i want is an automatic calculation for the total days allowed instead of manually putting the formula i want it to deduct the previous 5 years as pr the rules =5*15+(C8-5)*17 What is the formula that allows me to do that ? what i need is an automatic formula to be updated alone on the B12 Thx in advance- Hide quoted text - - Show quoted text - When you have 15 years of service you should get as a leave 15*5 for the first 5 years 17*5 for the 2nd 5 years 19*5 for the 3rd 5 years so i used this formula cause the first 5 years he should have 15 days only the second five he should get 17 im really to sorry to bother but the total should be 80.27 S/d is *C7=05/01/2003 * * * * C8==+(NOW()-C7)/30/12=5..31 cause =5*15+(C8-5)*17 __________=5*15+(5.31-5)*17=80.27 But when i use your formula i get =IF((TODAY()-C7)365/4,(ROUNDUP((TODAY()-C7)/365,0)*15)+ ((MAX(0,ROUNDUP((TODAY()-C7)/365,0)-5))*2)+((MAX(0,ROUNDUP((TODAY()- C7)/365,0)-10))*2),0)= 92- Hide quoted text - - Show quoted text - The 5 th year is counted as 17 days and teh 10th as 19 days If i uses this formula =5*15+(C8-5)*17 on the following date 24 Jul 2002 will gate 93.58 days but what i cant still find out is a formula that gives me this total automaticly whitout me putting it whenever an employee reaches his 5 th,10th or 15 th year as qan employee in our company |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating Leave
Well, you have pointedly ignored my point about the different yardsticks,
and persist in pushing that ludicrous formula that calculates based on a 12 month/30 day year (i.e. a year short of 5 or 6 days). If you used =(NOW()-C7)/30.5/12 it would be more accurate but still less accurate than using 365, so I am going to ignore that, offer what I have, and you can do as you will. Working straight off of the employment date, =ROUND(((TODAY()-C7)/365*15)+(INT(MAX(0,(TODAY()-C7)/365-5))*2)+(INT(MAX(0,(TODAY()-C7)/365-10))*2),2) -- __________________________________ HTH Bob "Tia" wrote in message ... On Jul 24, 8:21 pm, "Bob Phillips" wrote: BTW, does the 5th year only count as 15 days or 17 days? Similarly, Is the 10th 17 or 19? So for example, is someone joining on 24th July 2002 entitled to 92 days or 94? -- __________________________________ HTH Bob "Bob Phillips" wrote in message ... Another variation, part years now! I can get closer, but not exactly what you get because you are using two different yardsticks. This formula =IF(TODAY()-C710*365,19,IF(TODAY()-C75*365,17,IF(TODAY()-C70.25*365,15))*) is using a 365 day year, not totally accurate, but not far, but this one =(NOW()-C7)/30/12 uses a 360 day year, which is a huge 1.37% off. Using a consistent 365 day formula I get 78.53 for 5th May 2003. What do you want to do? -- __________________________________ HTH Bob "Tia" wrote in message ... On Jul 24, 2:16 pm, "Bob Phillips" wrote: Actually that can be a lot simpler =IF((TODAY()-C7)365/4, (ROUNDUP((TODAY()-C7)/365,0)*15) +((MAX(0,ROUNDUP((TODAY()-C7)/365,0)-5))*2) +((MAX(0,ROUNDUP((TODAY()-C7)/365,0)-10))*2),0) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... Correction =IF((TODAY()-C7)365/4, ((ROUNDUP((TODAY()-C7)/365,0)0)*15*ROUNDUP((TODAY()-C7)/365,0)) +((ROUNDUP((TODAY()-C7)/365,0)5)*(ROUNDUP((TODAY()-C7)/365,0)-5)*2) +((ROUNDUP((TODAY()-C7)/365,0)10)*(ROUNDUP((TODAY()-C7)/365,0)-10)*2),0) -- __________________________________ HTH Bob "Tia" wrote in message ... Hi I am trying to do annual leave calculation what i need is an automatic calculation for the following The rule is Every employee is entitled to a fully paid annual vacation after the completion of 3 months 1 to 5 years of consecutive service fifteen (15) days per year 5 to 10 years of consecutive service seventeen (17) days per year 10 to 15 years of consecutive service nineteen (19) days per year B C D 4 Name George 5 Staff Number 50004 6 Position 7 Starting Date 01-May-03 8 Total Working Days (NOW()-C7)/30/12 9 Annual Leave Days Taken Days Remaining 10 15 +D39 B11- C10 11 Total Days Allowed 12 =5*15+(C8-5)*17 39 From Day Inclusive To Day Exclusive Total Annual Leave A8 =The formula that i am using to find the Annual Leave pr year is the following =IF(TODAY()-C710*365,19,IF(TODAY()-C75*365,17,IF(TODAY()- C70.25*365,15))) What i want is an automatic calculation for the total days allowed instead of manually putting the formula i want it to deduct the previous 5 years as pr the rules =5*15+(C8-5)*17 What is the formula that allows me to do that ? what i need is an automatic formula to be updated alone on the B12 Thx in advance- Hide quoted text - - Show quoted text - When you have 15 years of service you should get as a leave 15*5 for the first 5 years 17*5 for the 2nd 5 years 19*5 for the 3rd 5 years so i used this formula cause the first 5 years he should have 15 days only the second five he should get 17 im really to sorry to bother but the total should be 80.27 S/d is C7=05/01/2003 C8==+(NOW()-C7)/30/12=5.31 cause =5*15+(C8-5)*17 __________=5*15+(5.31-5)*17=80.27 But when i use your formula i get =IF((TODAY()-C7)365/4,(ROUNDUP((TODAY()-C7)/365,0)*15)+ ((MAX(0,ROUNDUP((TODAY()-C7)/365,0)-5))*2)+((MAX(0,ROUNDUP((TODAY()- C7)/365,0)-10))*2),0)= 92- Hide quoted text - - Show quoted text - The 5 th year is counted as 17 days and teh 10th as 19 days If i uses this formula =5*15+(C8-5)*17 on the following date 24 Jul 2002 will gate 93.58 days but what i cant still find out is a formula that gives me this total automaticly whitout me putting it whenever an employee reaches his 5 th,10th or 15 th year as qan employee in our company |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Leave Log | Excel Discussion (Misc queries) | |||
Leave Tracker | Excel Discussion (Misc queries) | |||
Leave Calendar | Excel Discussion (Misc queries) | |||
Time To Leave | Excel Discussion (Misc queries) | |||
Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions |