Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Desperately trying to build a paid time off accrual worksheet...
I work for a company that owns hotels and casinos all over the States.
Company wide I would say we have about 13,000 employees. The hotel I work at has around 125 employees (even w/ the constant turnover after Katrina hit). My problem is, that my corporate office refuses to update our payroll operations. We still use punch time cards that every other Friday I have to manually enter time into the payroll server timesheet. Our system does not calculate paid time off, among a lot of other things. My employees come to me almost daily asking if I can tell them how many PTO days they have left. The only way I have to figure it out is by going through every PTO bi-weekly request form spreadsheet until I have added up the days taken throughout their service year. I am trying to build a spreadsheet in Excel 2003, that consists of: Emp#, Name, Job Class, Hourly Rate, Hire Date, Birthdate, Last Review Date, PTO per yr, PTO taken, PTO days left....among other personnel information. I want to create a formula that will calculate the PTO per yr the employee has. After 6 months = 2 days 1 year = 10 days 2 years = 14 days 6 years = 19 days 16 years = 25 days 26 years = 30 days (If the employee does not use the days within their service year, they loose it.) I also want in the PTO taken column a formula that will pull the # of days taken from the PTO request spreadsheet that I have to send in to home office. The employee's differ every spreadsheet, as does their placement. Once the days taken are in their proper column, I want the PTO left to be calculated from PTO days per year minus the days taken. I hope someone can help, I am at a total loss! -- Thank you!! Crystal |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Desperately trying to build a paid time off accrual worksheet...
Hi Crystal,
I would say get organized ... and go step by step ... Number One : insert a new worksheet where you will add your rules ... i.e the table PTO per employment year Number Two : in your original worksheet, add a column PTO rights, where you will have a lookup formula which will bring back what each employee is entiltled to ... And then, you will keep on refining your worksheet ... HTH Cheers Carim |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Desperately trying to build a paid time off accrual worksheet...
If my previous mail is clear to you ... now you can start solving your
first issue ... From a practical standpoint : 1. building your PTO Rule, I would have everything converted into months : Months Days 6 2 12 10 24 14 36 19 192 25 312 30 2. Then, I would highlight the range starting from 6 down to 30 and Insert Name Define and give the name PTORule to this range. 3. Then, back to the original worksheet, add a column PTO Rights, and assuming the Hire Date is located in column E , the formula would be : =VLOOKUP(DATEDIF(E16,TODAY(),"Y")*12+DATEDIF(E16,T ODAY(),"YM"),PTORule,2,1) If you get this to work properly, then you will move on ... HTH Cheers Carim |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Desperately trying to build a paid time off accrual worksheet.
Thank you! I'm going to start on it now, will let you know....
-- Thank you!! Crystal "Carim" wrote: If my previous mail is clear to you ... now you can start solving your first issue ... From a practical standpoint : 1. building your PTO Rule, I would have everything converted into months : Months Days 6 2 12 10 24 14 36 19 192 25 312 30 2. Then, I would highlight the range starting from 6 down to 30 and Insert Name Define and give the name PTORule to this range. 3. Then, back to the original worksheet, add a column PTO Rights, and assuming the Hire Date is located in column E , the formula would be : =VLOOKUP(DATEDIF(E16,TODAY(),"Y")*12+DATEDIF(E16,T ODAY(),"YM"),PTORule,2,1) If you get this to work properly, then you will move on ... HTH Cheers Carim |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Desperately trying to build a paid time off accrual worksheet...
We believe you have more time than you sound.
You are responsible to send the PTO request spreadsheet in to home office only at interval. We are not sure who prepares the request spreadsheet. Nevertheless, we would suggest to streamline the workflow as follow: In the spreadsheet, add one more column thus PTO per yr, PTO taken, PTO applied, PTO days left obviously days left = per yr - taken - applied every time an authorized user opens this file, it updates: 1, anyone crosses the entitlement scale? (how nice the employee feels when informed, if he has not hardwired this in his mind) 2, whether the entitlement is due to reset? (an employee will be upset when told of the correction afterwards, even if he already know) the authorized user can view Name, PTO per yr, PTO taken, PTO applied, PTO days left among other columns the authorized user can key in PTO applied and PTO days left is adjusted automatically (since the employee comes to ask everyday) At a desired interval, the authorized user triggers the system to generate PTO request record for home office. At this time, 3, the system adds PTO applied to PTO taken 4, reset PTO applied to nil This is necessary because we do not want to lose any count (the company may be unhappy) nor we want to double count (the employee will be VERY unhappy) "cgautreau" wrote in message ... I work for a company that owns hotels and casinos all over the States. Company wide I would say we have about 13,000 employees. The hotel I work at has around 125 employees (even w/ the constant turnover after Katrina hit). My problem is, that my corporate office refuses to update our payroll operations. We still use punch time cards that every other Friday I have to manually enter time into the payroll server timesheet. Our system does not calculate paid time off, among a lot of other things. My employees come to me almost daily asking if I can tell them how many PTO days they have left. The only way I have to figure it out is by going through every PTO bi-weekly request form spreadsheet until I have added up the days taken throughout their service year. I am trying to build a spreadsheet in Excel 2003, that consists of: Emp#, Name, Job Class, Hourly Rate, Hire Date, Birthdate, Last Review Date, PTO per yr, PTO taken, PTO days left....among other personnel information. I want to create a formula that will calculate the PTO per yr the employee has. After 6 months = 2 days 1 year = 10 days 2 years = 14 days 6 years = 19 days 16 years = 25 days 26 years = 30 days (If the employee does not use the days within their service year, they loose it.) I also want in the PTO taken column a formula that will pull the # of days taken from the PTO request spreadsheet that I have to send in to home office. The employee's differ every spreadsheet, as does their placement. Once the days taken are in their proper column, I want the PTO left to be calculated from PTO days per year minus the days taken. I hope someone can help, I am at a total loss! -- Thank you!! Crystal |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Desperately trying to build a paid time off accrual worksheet...
Without any doubt, the previous post is a very professional comment
with a lot of added value ... |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Desperately trying to build a paid time off accrual worksheet.
Thank you so much! I appreciate everyone trying to help me. Unfortunately,
our employees will not be able to have access to the spreadsheets. The way that they request for days off is by turning in a request sheet to me. I compile the requests until the end of that payperiod and enter the information in a simple spreadsheet. The spreadsheet consists of: Employee name, Emp.#, Job Class, and how many days they are requesting. I then email that spreadsheet to my corporate office. I want to be able to pull from these spreadsheets and have their information in a master spreadsheet that tells me how many days they are entitled to, how many days they have taken and how many days they have left. I wish that we had the capability to let everyone have access to their information. Unfortunately, that will not happen any time soon. I really do appreciate any help that you can give. I'm still working on getting this to work..... -- Thank you!! Crystal "PY & Associates" wrote: We believe you have more time than you sound. You are responsible to send the PTO request spreadsheet in to home office only at interval. We are not sure who prepares the request spreadsheet. Nevertheless, we would suggest to streamline the workflow as follow: In the spreadsheet, add one more column thus PTO per yr, PTO taken, PTO applied, PTO days left obviously days left = per yr - taken - applied every time an authorized user opens this file, it updates: 1, anyone crosses the entitlement scale? (how nice the employee feels when informed, if he has not hardwired this in his mind) 2, whether the entitlement is due to reset? (an employee will be upset when told of the correction afterwards, even if he already know) the authorized user can view Name, PTO per yr, PTO taken, PTO applied, PTO days left among other columns the authorized user can key in PTO applied and PTO days left is adjusted automatically (since the employee comes to ask everyday) At a desired interval, the authorized user triggers the system to generate PTO request record for home office. At this time, 3, the system adds PTO applied to PTO taken 4, reset PTO applied to nil This is necessary because we do not want to lose any count (the company may be unhappy) nor we want to double count (the employee will be VERY unhappy) "cgautreau" wrote in message ... I work for a company that owns hotels and casinos all over the States. Company wide I would say we have about 13,000 employees. The hotel I work at has around 125 employees (even w/ the constant turnover after Katrina hit). My problem is, that my corporate office refuses to update our payroll operations. We still use punch time cards that every other Friday I have to manually enter time into the payroll server timesheet. Our system does not calculate paid time off, among a lot of other things. My employees come to me almost daily asking if I can tell them how many PTO days they have left. The only way I have to figure it out is by going through every PTO bi-weekly request form spreadsheet until I have added up the days taken throughout their service year. I am trying to build a spreadsheet in Excel 2003, that consists of: Emp#, Name, Job Class, Hourly Rate, Hire Date, Birthdate, Last Review Date, PTO per yr, PTO taken, PTO days left....among other personnel information. I want to create a formula that will calculate the PTO per yr the employee has. After 6 months = 2 days 1 year = 10 days 2 years = 14 days 6 years = 19 days 16 years = 25 days 26 years = 30 days (If the employee does not use the days within their service year, they loose it.) I also want in the PTO taken column a formula that will pull the # of days taken from the PTO request spreadsheet that I have to send in to home office. The employee's differ every spreadsheet, as does their placement. Once the days taken are in their proper column, I want the PTO left to be calculated from PTO days per year minus the days taken. I hope someone can help, I am at a total loss! -- Thank you!! Crystal |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Desperately trying to build a paid time off accrual worksheet.
You must have misunderstood.
The authorized user is you, not everybody. If you pull data from request forms to the record file, you need to know each filename; whether that data in a particular file have been "pulled". Do not forget to pull; Do not pull twice. If you "compile" the request, you can update the record file at the same time (manually or programmatically), but you cannot "see" the balance days "real time" "cgautreau" wrote in message ... Thank you so much! I appreciate everyone trying to help me. Unfortunately, our employees will not be able to have access to the spreadsheets. The way that they request for days off is by turning in a request sheet to me. I compile the requests until the end of that payperiod and enter the information in a simple spreadsheet. The spreadsheet consists of: Employee name, Emp.#, Job Class, and how many days they are requesting. I then that spreadsheet to my corporate office. I want to be able to pull from these spreadsheets and have their information in a master spreadsheet that tells me how many days they are entitled to, how many days they have taken and how many days they have left. I wish that we had the capability to let everyone have access to their information. Unfortunately, that will not happen any time soon. I really do appreciate any help that you can give. I'm still working on getting this to work..... -- Thank you!! Crystal "PY & Associates" wrote: We believe you have more time than you sound. You are responsible to send the PTO request spreadsheet in to home office only at interval. We are not sure who prepares the request spreadsheet. Nevertheless, we would suggest to streamline the workflow as follow: In the spreadsheet, add one more column thus PTO per yr, PTO taken, PTO applied, PTO days left obviously days left = per yr - taken - applied every time an authorized user opens this file, it updates: 1, anyone crosses the entitlement scale? (how nice the employee feels when informed, if he has not hardwired this in his mind) 2, whether the entitlement is due to reset? (an employee will be upset when told of the correction afterwards, even if he already know) the authorized user can view Name, PTO per yr, PTO taken, PTO applied, PTO days left among other columns the authorized user can key in PTO applied and PTO days left is adjusted automatically (since the employee comes to ask everyday) At a desired interval, the authorized user triggers the system to generate PTO request record for home office. At this time, 3, the system adds PTO applied to PTO taken 4, reset PTO applied to nil This is necessary because we do not want to lose any count (the company may be unhappy) nor we want to double count (the employee will be VERY unhappy) "cgautreau" wrote in message ... I work for a company that owns hotels and casinos all over the States. Company wide I would say we have about 13,000 employees. The hotel I work at has around 125 employees (even w/ the constant turnover after Katrina hit). My problem is, that my corporate office refuses to update our payroll operations. We still use punch time cards that every other Friday I have to manually enter time into the payroll server timesheet. Our system does not calculate paid time off, among a lot of other things. My employees come to me almost daily asking if I can tell them how many PTO days they have left. The only way I have to figure it out is by going through every PTO bi-weekly request form spreadsheet until I have added up the days taken throughout their service year. I am trying to build a spreadsheet in Excel 2003, that consists of: Emp#, Name, Job Class, Hourly Rate, Hire Date, Birthdate, Last Review Date, PTO per yr, PTO taken, PTO days left....among other personnel information. I want to create a formula that will calculate the PTO per yr the employee has. After 6 months = 2 days 1 year = 10 days 2 years = 14 days 6 years = 19 days 16 years = 25 days 26 years = 30 days (If the employee does not use the days within their service year, they loose it.) I also want in the PTO taken column a formula that will pull the # of days taken from the PTO request spreadsheet that I have to send in to home office. The employee's differ every spreadsheet, as does their placement. Once the days taken are in their proper column, I want the PTO left to be calculated from PTO days per year minus the days taken. I hope someone can help, I am at a total loss! -- Thank you!! Crystal |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Desperately trying to build a paid time off accrual worksheet.
On second thought, you may consider this:
With due respect, we use the term "you" instead of "an authorized user" and guess (suggest) your workflow thus: you receive request sheets from staff (this is hard copy or soft copy, does not matter); [The way that they request for days off is by turning in a request sheet to me.] Instead of compiling into a separate spreadsheet, you key the info directly into the record sheet as we suggested earlier; [I compile the requests until the end of that payperiod and enter the information in a simple spreadsheet.] This way you are always up to date; You do not have blind spot before end of that pay period; Staff can change their mind any time up to uploading day; You keep the request sheet for audit trail just in case there is "forgetful" staff. You generate from the master file for uploading to corporate office. [I then email that spreadsheet to my corporate office.] You do not pull data from two spreadsheets, you push data from one single master sheet. [I want to be able to pull from these spreadsheets and have their information in a master spreadsheet that tells me how many days they are entitled to, how many days they have taken and how many days they have left.] "PY & Associates" wrote in message ... You must have misunderstood. The authorized user is you, not everybody. If you pull data from request forms to the record file, you need to know each filename; whether that data in a particular file have been "pulled". Do not forget to pull; Do not pull twice. If you "compile" the request, you can update the record file at the same time (manually or programmatically), but you cannot "see" the balance days "real time" "cgautreau" wrote in message ... Thank you so much! I appreciate everyone trying to help me. Unfortunately, our employees will not be able to have access to the spreadsheets. The way that they request for days off is by turning in a request sheet to me. I compile the requests until the end of that payperiod and enter the information in a simple spreadsheet. The spreadsheet consists of: Employee name, Emp.#, Job Class, and how many days they are requesting. I then that spreadsheet to my corporate office. I want to be able to pull from these spreadsheets and have their information in a master spreadsheet that tells me how many days they are entitled to, how many days they have taken and how many days they have left. I wish that we had the capability to let everyone have access to their information. Unfortunately, that will not happen any time soon. I really do appreciate any help that you can give. I'm still working on getting this to work..... -- Thank you!! Crystal "PY & Associates" wrote: We believe you have more time than you sound. You are responsible to send the PTO request spreadsheet in to home office only at interval. We are not sure who prepares the request spreadsheet. Nevertheless, we would suggest to streamline the workflow as follow: In the spreadsheet, add one more column thus PTO per yr, PTO taken, PTO applied, PTO days left obviously days left = per yr - taken - applied every time an authorized user opens this file, it updates: 1, anyone crosses the entitlement scale? (how nice the employee feels when informed, if he has not hardwired this in his mind) 2, whether the entitlement is due to reset? (an employee will be upset when told of the correction afterwards, even if he already know) the authorized user can view Name, PTO per yr, PTO taken, PTO applied, PTO days left among other columns the authorized user can key in PTO applied and PTO days left is adjust ed automatically (since the employee comes to ask everyday) At a desired interval, the authorized user triggers the system to generate PTO request record for home office. At this time, 3, the system adds PTO applied to PTO taken 4, reset PTO applied to nil This is necessary because we do not want to lose any count (the company may be unhappy) nor we want to double count (the employee will be VERY unhappy) "cgautreau" wrote in message ... I work for a company that owns hotels and casinos all over the States. Company wide I would say we have about 13,000 employees. The hotel I work at has around 125 employees (even w/ the constant turnover after Katrina hit). My problem is, that my corporate office refuses to update our payroll operations. We still use punch time cards that every other Friday I have to manually enter time into the payroll server timesheet. Our system does not calculate paid time off, among a lot of other things. My employees come to me almost daily asking if I can tell them how many PTO days they have left. The only way I have to figure it out is by going through every PTO bi-weekly request form spreadsheet until I have added up the days taken throughout their service year. I am trying to build a spreadsheet in Excel 2003, that consists of: Emp#, Name, Job Class, Hourly Rate, Hire Date, Birthdate, Last Review Date, PTO per yr, PTO taken, PTO days left....among other personnel information. I want to create a formula that will calculate the PTO per yr the employee has. After 6 months = 2 days 1 year = 10 days 2 years = 14 days 6 years = 19 days 16 years = 25 days 26 years = 30 days (If the employee does not use the days within their service year, they loose it.) I also want in the PTO taken column a formula that will pull the # of days taken from the PTO request spreadsheet that I have to send in to home office. The employee's differ every spreadsheet, as does their placement. Once the days taken are in their proper column, I want the PTO left to be calculated from PTO days per year minus the days taken. I hope someone can help, I am at a total loss! -- Thank you!! Crystal |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Desperately need your help
Dear Friends, I'm stuck with this formula.. can you please help me? here is the worksheet: A1 = 7:00 A2 = 18:00 B1 = IN B2 = OUT B3 = MORNING OVER TIME B4 = AFTERNOON OVERTIME B5 = TOTAL OVERTIME what I've done: B3 = $A$1-B1 B4 = $A$2-B2 B5 = B3+B4 However, for the B3 formula, it keeps showing "######" as the result (for negative result), therefore, I cannot find the total overtime correctly. can you help me please? thank you in advanced. Rh33a |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing a list to a Calendar worksheet. | Excel Worksheet Functions | |||
How do I open to a specific worksheet every time? | New Users to Excel | |||
Unhide more than one worksheet at a time | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
How do I get real time stock quotes to my excel worksheet? | Excel Discussion (Misc queries) |