Vacation Hour Formula

[Gtrtim112]

PART 1

I currently have a spreadsheet that I use for employee vacations. Our vacations are based on hire dates in lieu of the calendar year. Currently, the following formula is in place to properly figure the employee's eligible balance.

=IF(E2="FT",LOOKUP(DATEDIF(C2,TODAY(),"y"),{0,1,2,9,18;0,40,80,120,160}),0)

This basically states that if the employee is full time (hence FT), they will receive vacation time based on their hire date and today's date. 1 year will get them 40 hours, 2 years gets them 80 hours, etc.

Our policy is changing so my formula will need to also. The only difference is that now, FT employees will receive 24 hours of vacation after 90 days. The rest stays the same. The one sidenote on this is that it DOES NOT include supervisors, which are salaried employees, only hourly. In my attached spreadsheet. So, I'll need the formula to exclude anyone who is a supervisor... this information is noted in a column on the attached spreadsheet.

Part 2

We also have personal days. These are based off of calendar year and the formula I currently use is..

=IF(AND(E2="FT",YEAR(C2)<YEAR(TODAY()),D2<>"Supervisor"),16,0)

This basically states that anyone that is full time, with the exception of supervisors, will receive 16 hours of personal days at the beginning of each year.

Our policy on this is changing also. The difference now is that we will also give part time (PT) DOCK employees 24 hours of personal days. This 24 hours is accrued in one hour increments. For every 30 hours worked, they gain 1 hour of personal day time. I currently don't have a column for "Hours Worked" but can add one.

****************************

So, I know that's a lot to ask for but I've yet to stump anyone in these message forums. Every time I've posted something difficult, someone has been able to figure it out. I'm hoping for the same here.

I've attached my example spreadsheet.

Thanks for taking a look!

[Richard Buttrey]

Hi,

Does this work for Part1 in G2 copied down.

Please Login or Register  to view this content.

I don't fully understand your 2nd part.

Would you add some typical examples and also manually add in a spare column the result you would expect to see in column G. And indeed if the answer above isn't correct upload the workbook with the correct answers too.

[Gtrtim112]

Hi,

Does this work for Part1 in G2 copied down.

Please Login or Register  to view this content.

Almost but not quite... It's my mistake for not fully explaining. Let me try to explain once more on this part. I think it will help you.

All full time (FT) employees should receive 24 hours of vacation time after 90 days of employment with the exception of Supervisors. Supervisors receive 40 hours immediately upon being hired. After that, all employees accrue vacation at the same rate... 1 year = 40 hours / 2 years = 80 hours / You see my figures on that already, I think.

I don't fully understand your 2nd part.

Would you add some typical examples and also manually add in a spare column the result you would expect to see in column G. And indeed if the answer above isn't correct upload the workbook with the correct answers too.

Yes, I can add an example. I will attach one here

I will try to explain this one again also...

After 90 days of employment, PT employees receive 1 hour of Personal Day (PD) time for every 30 hours worked, up to a maximum of 24 hours of PD time. All FT employees receive 16 hours of PD time after 90 days of employment. The exception to this rule is SUPERVISORS. They do not get PD time at all.

Hope that helps!

[Richard Buttrey]

Does this give you what you want?

If not please add in some adjacent column the results you expect for any of the columns which aren't giving the right results and explain the calculation.

[Gtrtim112]

Does this give you what you want?

If not please add in some adjacent column the results you expect for any of the columns which aren't giving the right results and explain the calculation.

Super close! The PD time looks correct. I ran it through a battery of scenarios to try and trick it and everything seemed to work great. There's just one little glitch with the Vacation time when it comes to Supervisors. I put the correct amount in the example I've attached. In short, though, Supervisors get 40 hours on their hire date while all others get 24 hours after 90 days. The formula is adding 40 + 24 for Supervisors after 90 days of employment, giving them 64 total hours of vacation. They should stay at 40 all the way to the 2 year anniversary just like the other employees.

Hope that helps!

Thanks for the assistance!

[Richard Buttrey]

Hi,

Try in G6, copied down

Please Login or Register  to view this content.

[Gtrtim112]

Hi,

Try in G6, copied down

Please Login or Register  to view this content.

Richard... This didn't do the trick. Now, Supervisor doesn't show 40 hours immediately after being hired and PT is producing a "false" result. I've attached the example for you.

[Gtrtim112]

Richard,

Did I stump yo on this one?

[Gtrtim112]

Lem,

Will you be able to rescue me?

[FlameRetired]

I worked with the workbook uploaded in Post #3.

I copied and pasted values from G7:G12 to G27:G32 for comparisons.

I added these lookup tables in $T$12:$U$17 and $V$12:$W$16. Non-supervisors and Supervisors

Row\Col	T	U	V	W
12	0	0	0	40
13	0.246407	24	1	40
14	1	40	2	80
15	2	80	9	120
16	9	120	18	160
17	18	160

I put this formula in G7 and filled down to G12 replacing the originals. Does this do what you want?

Please Login or Register  to view this content.

It produced this:

Row\Col	C	D	E	F	G
7	01/02/15	Supervisor	FT	0	40
8	04/21/14	Dock	FT	0	24
9	06/15/92	Hub	FT	0	160
10	06/16/08	City	FT	0	80
11	05/01/00	Linehaul	FT	0	120
12	04/11/14	Combo	FT	0	24

The file is attached.

[Gtrtim112]

I worked with the workbook uploaded in Post #3.

I copied and pasted values from G7:G12 to G27:G32 for comparisons.

I added these lookup tables in $T$12:$U$17 and $V$12:$W$16. Non-supervisors and Supervisors

Row\Col	T	U	V	W
12	0	0	0	40
13	0.246407	24	1	40
14	1	40	2	80
15	2	80	9	120
16	9	120	18	160
17	18	160

I put this formula in G7 and filled down to G12 replacing the originals. Does this do what you want?

Please Login or Register  to view this content.

It produced this:

Row\Col	C	D	E	F	G
7	01/02/15	Supervisor	FT	0	40
8	04/21/14	Dock	FT	0	24
9	06/15/92	Hub	FT	0	160
10	06/16/08	City	FT	0	80
11	05/01/00	Linehaul	FT	0	120
12	04/11/14	Combo	FT	0	24

The file is attached.

By golly, I think you got it!

I'll run it through some more tests just to make sure and let you know... thanks!

[Gtrtim112]

It worked!! I've ran through many scenarios and it appears that they all have figured correctly... thank so much!

Richard, thanks for your contributions also!

[FlameRetired]

Glad it works and thanks for the rep!