| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel
How do I create an accural spread sheet for vacations.
this is the info that I have 1st yr - 1 week @ .77 per pay period 2nd - 10th yr - 2 weeks @ 1.54 per pay period 10th + 3 weeks @ 2.31 per pay period |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel
Rae,
Your question is not very clear, (at least to me). 1st yr - 1 week @ .77 per pay period Does that mean from the hire date to the 1st year anniversary the employee gets 1 weeks vacation? What does @ .77 per pay period mean? What is a pay period? Try restating your needs rmembering that we do not know how your company works. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Rae" wrote in message ... How do I create an accural spread sheet for vacations. this is the info that I have 1st yr - 1 week @ .77 per pay period 2nd - 10th yr - 2 weeks @ 1.54 per pay period 10th + 3 weeks @ 2.31 per pay period |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel
from the date of hire the employee starts to accural vacation time. After one
year of service they are entitled to 1 week. 1st year would be 52*.77=40 2nd - 10th would be 52*1.54 = 80 10th + would be 2.31*52 =120 What I want to do is create a spread sheet where I can enter the date of hire and it will calculate the vacation time. "Sandy Mann" wrote: Rae, Your question is not very clear, (at least to me). 1st yr - 1 week @ .77 per pay period Does that mean from the hire date to the 1st year anniversary the employee gets 1 weeks vacation? What does @ .77 per pay period mean? What is a pay period? Try restating your needs rmembering that we do not know how your company works. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Rae" wrote in message ... How do I create an accural spread sheet for vacations. this is the info that I have 1st yr - 1 week @ .77 per pay period 2nd - 10th yr - 2 weeks @ 1.54 per pay period 10th + 3 weeks @ 2.31 per pay period |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel
If you want to work out how many hours are due based on a weeks in the
currect vacation year then try: =INT(IF(DATEDIF(F11,TODAY(),"y")<1,40/52,IF(DATEDIF(F11,TODAY(),"y")<10,80/52,120/52))*CEILING((TODAY()-DATE(YEAR(F11)+DATEDIF(F11,TODAY(),"y"),MONTH(F11) ,DAY(F11)))/7,1)) format the cell as General -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Sandy Mann" wrote in message ... To return the number of hours due for the number of years of service try: =IF(DATEDIF(F11,TODAY(),"y")<1,40,IF(DATEDIF(F11,T ODAY(),"y")<10,80,120)) with the start date in F11 If you want something different then post back. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Rae" wrote in message ... from the date of hire the employee starts to accural vacation time. After one year of service they are entitled to 1 week. 1st year would be 52*.77=40 2nd - 10th would be 52*1.54 = 80 10th + would be 2.31*52 =120 What I want to do is create a spread sheet where I can enter the date of hire and it will calculate the vacation time. "Sandy Mann" wrote: Rae, Your question is not very clear, (at least to me). 1st yr - 1 week @ .77 per pay period Does that mean from the hire date to the 1st year anniversary the employee gets 1 weeks vacation? What does @ .77 per pay period mean? What is a pay period? Try restating your needs rmembering that we do not know how your company works. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Rae" wrote in message ... How do I create an accural spread sheet for vacations. this is the info that I have 1st yr - 1 week @ .77 per pay period 2nd - 10th yr - 2 weeks @ 1.54 per pay period 10th + 3 weeks @ 2.31 per pay period |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel
Slightly shorter and with fewer function calls and it also allows for F1
being empty: =IF(F11="","",INT(LOOKUP(DATEDIF(F11,TODAY(),"Y"), {0;1;10},{40,80,120})/52*CEILING((TODAY()-DATE(YEAR(F11)+DATEDIF(F11,TODAY(),"y"),MONTH(F11) ,DAY(F11)))/7,1))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Sandy Mann" wrote in message ... If you want to work out how many hours are due based on a weeks in the currect vacation year then try: =INT(IF(DATEDIF(F11,TODAY(),"y")<1,40/52,IF(DATEDIF(F11,TODAY(),"y")<10,80/52,120/52))*CEILING((TODAY()-DATE(YEAR(F11)+DATEDIF(F11,TODAY(),"y"),MONTH(F11) ,DAY(F11)))/7,1)) format the cell as General -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Sandy Mann" wrote in message ... To return the number of hours due for the number of years of service try: =IF(DATEDIF(F11,TODAY(),"y")<1,40,IF(DATEDIF(F11,T ODAY(),"y")<10,80,120)) with the start date in F11 If you want something different then post back. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Rae" wrote in message ... from the date of hire the employee starts to accural vacation time. After one year of service they are entitled to 1 week. 1st year would be 52*.77=40 2nd - 10th would be 52*1.54 = 80 10th + would be 2.31*52 =120 What I want to do is create a spread sheet where I can enter the date of hire and it will calculate the vacation time. "Sandy Mann" wrote: Rae, Your question is not very clear, (at least to me). 1st yr - 1 week @ .77 per pay period Does that mean from the hire date to the 1st year anniversary the employee gets 1 weeks vacation? What does @ .77 per pay period mean? What is a pay period? Try restating your needs rmembering that we do not know how your company works. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Rae" wrote in message ... How do I create an accural spread sheet for vacations. this is the info that I have 1st yr - 1 week @ .77 per pay period 2nd - 10th yr - 2 weeks @ 1.54 per pay period 10th + 3 weeks @ 2.31 per pay period |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
