Desperately trying to build a paid time off accrual worksheet...

[Guest]

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!

 

[Carim]

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

 

[Carim]

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,TODAY(),"YM"),PTORule,2,1)

If you get this to work properly, then you will move on ...

HTH
Cheers
Carim

 

[Guest]

Thank you! I'm going to start on it now, will let you know....

 

[PY & Associates]

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)

 

[Carim]

Without any doubt, the previous post is a very professional comment
with a lot of added value ...

 

[Guest]

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

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)

 

[PY & Associates]

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"

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.....

 

[PY & Associates]

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.]

 

[rh33a]

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