Vacation Days Accrued

[jprogrammer]

I have inherited a spreadsheet that accrues vacation time. However I'm
not sure if it works PROPERLY plus I don't understand it at all. Any
changes or advice would be very helpful! Here is ALL the criteria of
what the spreadsheet represents:

*During first calendar year vacation will accrue at 3.34 hours per
completed month of service. (40.08 hours)
*Beginning January 1 of the 10th calendar year vacation will accrue at
6.67 hours per completed month of service. (80.04 hours)


I have 3 columns set up:
*Column 1 holds the formula for employees who have been here 1 month-9
years.
*Column 2 holds the formula for employees who have been here >=10
Years.
*Column 3 holds the following formula which converts either column 1 or
column 2 into 40 or 80 hours depending on which the employee qualifies.

code:

Column 1 Formula (1 month-9 Years):
Code:
--------------------
=IF(AND(DAY(TODAY())>=DAY(E6),YEAR(TODAY())=YEAR(E6)),(MONTH(TODAY())-MONTH(E6))*3.34,IF(AND(DAY(TODAY())<DAY(E6),YEAR(TODAY())=YEAR(E6)),(MONTH(TODAY())-(MONTH(E6)+1))*3.34,IF(AND(YEAR(TODAY()>YEAR(E6)),(YEAR(TODAY())-YEAR(E6)<10),DAY(TODAY())>=DAY(E6)),(((MONTH(TODAY()))*(80/12))+((12-MONTH(E6))*3.34)+((YEAR(TODAY())-(YEAR(E6)+1))*80)),IF(AND(YEAR(TODAY()>YEAR(E6)),(YEAR(TODAY())-YEAR(E6)<10),DAY(TODAY())<DAY(E6)),(((MONTH(TODAY())-1)*(80/12))+((12-MONTH(E6))*3.34)+((YEAR(TODAY())-(YEAR(E6)+1))*40)),"N/A"))))
--------------------


Column 2 Formula (>=10 Years):
Code:
--------------------
=IF(AND((YEAR(TODAY())-YEAR(E6)>=10),DAY(TODAY())>=DAY(E6)),(((MONTH(TODAY()))*(10))+((12-MONTH(E6))*6.67)+((YEAR(TODAY())-(YEAR(E6)+10))*80)+720),IF(AND((YEAR(TODAY())-YEAR(E6)>=10),DAY(TODAY())<DAY(E6)),(((MONTH(TODAY())-1)*(10))+((12-MONTH(E6))*6.67)+((YEAR(TODAY())-(YEAR(E6)+10))*80)+720),"N/A"))
--------------------


Column 3 Formula (Converts Column 1 OR 2 into 40 OR 80 hours):
Code:
--------------------
=IF(S6<=40,S6,IF(S6="N/A",80,IF(S6>40,40,"???")))
--------------------


I hope I haven't confused everyone! This is hard for me to follow much
less trying to explain to others.

Thanks for taking a look at this!

 

[Bernie Deitrick]

jp,

As far as I can tell, both of the formulas are whacked. I'm assuming that the employee's service
date is in cell E6: in that case, your formulas return inconsistent values. For example, for these
two anniversary dates, one week apart about two years ago, instead of 80.16 (two years worth of
vacation at 40 hrs/year), your first formula returns 160 for the later date, and 116.67 for the
earlier date - off by 80 and 36 hours respectively.

12/3/2003 160
11/26/2003 116.67

Things get even screwier for earlier dates: for this pair, we get vacation accruals of:

4/1/1998 586.72
3/25/1998 343.3933

instead of 307 or so that should be given, off by 279 and 36 hours respectively.

Note that the vacation times are always in the employee's favor, so you may want to ignore these
errors ;-)


Anyway, both of those monstrosities could possibly be replaced by one formula, in S6:

=IF(DATEDIF(E6,TODAY(),"y")<10,DATEDIF(E6,TODAY(),"m")*3.34,400+DATEDIF(DATE(YEAR(E6)+10,MONTH(E6),DAY(E6)),TODAY(),"m")*6.67)

but then you would need to replace

=IF(S6<=40,S6,IF(S6="N/A",80,IF(S6>40,40,"???")))

with

=IF(DATEDIF(E6,TODAY(),"y")<10,MIN(40,S6),80))

Not sure why the ??? was thrown in, perhaps if E6 is blank, S6 may return odd errors....

HTH,
Bernie
MS Excel MVP

 

[jprogrammer]

Thank you SO much Bernie! I'm getting an error (#NAME?) in S6 sometimes
though. I'm trying to figure it out but I'm not coming up with any
fixes. Any idea why this is throwing an error?

Again, THANK YOU so much for your help!!!

 

[jprogrammer]

I figured out why the error #NAME? was happening. There was just a
space between some text. So far this works great!

Thank you SO MUCH!!! You are a life saver!

 

[jprogrammer]

I have one more question Bernie. I'm trying to learn as I go here. I
the formula:

Code
-------------------
=IF(DATEDIF(E6,TODAY(),"y")<10,DATEDIF(E6,TODAY(), "m")*3.34,400+DATEDIF(DATE(YEAR(E6)+10,MONTH(E6),D AY(E6)),TODAY(),"m")*6.67
-------------------


What does the 400 represent?

Thanks

 

[Cutter]

Just a guess on my part:

40 hrs per year for the first 10 years?

 

[Guest]

Your formulas work GREAT!!! Now I'm just trying to understand how they work
exactly. I don't want to just take your code w/o learning from it.

I understand how time is accrued for people that have been employed for 1
month -9 years work. 11/25/2002 = 120.24 (3.34 * 36 months) but I'm not
understanding how time is being accrued for people that have been employed 10
years or more. I've tried using the same mathematical approach but it's not
working. For example- 5/5/1997 = 344.02. I would assume that it would be
280.56 since 3.34 * 84 months = 280.56. Can you help me understand this
clearer?

=IF(DATEDIF(E6,TODAY(),"y")<10,DATEDIF(E6,TODAY(),"m")*3.34,400+DATEDIF(DATE(YEAR(E6)+10,MONTH(E6),DAY(E6)),TODAY(),"m")*6.67)


Thanks again for all your help!!!

jp,

As far as I can tell, both of the formulas are whacked. I'm assuming that the employee's service
date is in cell E6: in that case, your formulas return inconsistent values. For example, for these
two anniversary dates, one week apart about two years ago, instead of 80.16 (two years worth of
vacation at 40 hrs/year), your first formula returns 160 for the later date, and 116.67 for the
earlier date - off by 80 and 36 hours respectively.

12/3/2003 160
11/26/2003 116.67

Things get even screwier for earlier dates: for this pair, we get vacation accruals of:

4/1/1998 586.72
3/25/1998 343.3933

instead of 307 or so that should be given, off by 279 and 36 hours respectively.

Note that the vacation times are always in the employee's favor, so you may want to ignore these
errors ;-)


Anyway, both of those monstrosities could possibly be replaced by one formula, in S6:

=IF(DATEDIF(E6,TODAY(),"y")<10,DATEDIF(E6,TODAY(),"m")*3.34,400+DATEDIF(DATE(YEAR(E6)+10,MONTH(E6),DAY(E6)),TODAY(),"m")*6.67)

but then you would need to replace

=IF(S6<=40,S6,IF(S6="N/A",80,IF(S6>40,40,"???")))

with

=IF(DATEDIF(E6,TODAY(),"y")<10,MIN(40,S6),80))

Not sure why the ??? was thrown in, perhaps if E6 is blank, S6 may return odd errors....

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

jp (or is it np?),

5/5/97 is 103 months ago, not 84:

7 in 97, 12 each in 98, 99, 2000, 01, 02, 03, 04, and 12 again in 05.

103 * 3.34 = 344.02

P.S. You could improve the accuracy of the formulas by using 3 1/3 and 6
2/3 instead of the decimals....

Bernie
MS Excel MVP

 

[Bernie Deitrick]

Cutter,

Ding ding ding ding! We have a winner... you are correct.

Bernie

 

[jprogrammer]

You can call me either Bernie. I use one username for this forum
a different one for the microsoft forum.

Thanks again for your help on this!!! I understand better what thi
formula is doing now. Works great!

I have a similar worksheet that is for office employees only. So th
criteria is a little different. You've already helped me with th
first bullet below but how could I incorporate the following 2 bullet
with the first bullet? See below:

Thanks!

 

[Bernie Deitrick]

jp,

I may have mis-interpreted your criteria. When you said:

Beginning January 1 of the 10th calendar year vacation will accrue at
6.67 hours per completed month of service. (80.04 hours)

Does that mean that if the tenth anniversary will occur in December, the employee uses the higher
rate for that whole calendar year, starting in January, just after the completion of their 9th year?

The formula that I provided starts the 80 hrs/year rate after the tenth anniversary (that is how my
vacation system works), which could result in the loss of up to almost 40 hours of vacation time to
someone whose anniversary date is late in the year. But it would treat two workers who started days
apart, one before Dec 31 and one after Jan 1, unfairly.

Let me know how exactly the criteria should be interpreted....

HTH,
Bernie
MS Excel MVP

 

[jprogrammer]

The criteria that I gave this morning is different than the criteri
that I gave yesterday.

This is for hourly field employees (yesterday):

*During first calendar year vacation will accrue at 3.34 hours per
completed month of service. (40.08 hours)

*Beginning January 1 of the 10th calendar year vacation will accrue at
6.67 hours per completed month of service. (80.04 hours)

This is for Office Hourly and Salaried employees (Today):

*During first calendar year vacation will accrue at 3.34 hours pe
complete month of service.

*Beginning January 1 following the date of hire- employee will receiv
80 hours each year.

*Beginning January 1 of tenth calendar year of service- employee wil
receive 120 hours each year.

I hope this helps?

Thanks so much for your help

 

[Bernie Deitrick]

jp,

The criteria that I gave this morning is different than the criteria
that I gave yesterday.

I understand that, but I'm concerned about how I used yesterday's criteria. When does the 80 hours
kick in - January 1, or on the anniversary date?

Bernie

 

[jprogrammer]

January 1st.

Thanks! :-

 

[jprogrammer]

Hire Date: 02/09/1979 should be 2147.74 hours, yet in the formula i
returning 1747.34. Is this what you were talking about in you
previous post?

Sorry to bug you so much with this! I REALLY appriciate your help! :

 

[jprogrammer]

Ok if I change the 400 to 800 the formula works for everyone includin
people that have been with the company for 10 years or more.


Code
-------------------
=IF(DATEDIF(E6,TODAY(),"y")<10,DATEDIF(E6,TODAY(), "m")*3.34,800+DATEDIF(DATE(YEAR(E6)+10,MONTH(E6),DAY(E6)),TODAY(),"m")*6.67
-------------------


My question is if I change this to 800 to make the formula work, am
just changing it to "make it work" or is this a logical approach tha
will work in all scenerio's?

Thanks,
j

 

[Bernie Deitrick]

How do you get 2147.74?

2/9/1979 was 27 years ago, in round number: 10 years at 40 hrs, 17 years at 80 gives

=10*40+17*80

1760

Even with the January 1 change, I get (best case):

=9*40+18*80

1800


Anyway, this version changes from the 10 year to the January 1 accrual:

=IF(DATEDIF(DATE(YEAR(D7),1,1),TODAY(),"y")<10,DATEDIF(D7,TODAY(),"m")*3.34,DATEDIF(D7,DATE(YEAR(D7)+9,12,31),"m")*3.34
+DATEDIF(DATE(YEAR(D7)+10,1,1),TODAY(),"m")*6.67)


Bernie
MS Excel MVP

 

[jprogrammer]

Thanks so much again Bernie! This works great!!!

Ok so I learn from this can you tell me why when the date is 10 year
or greater, the formula returns a 0 rather than the actual tim
accrued? I think I understand it all but that. :

 

[jprogrammer]

I tweaked the following formula so when the actual accrue is less than
40 hours, the cell will reflect that.


Code:
--------------------
=IF(E2<=40, E2, IF(DATEDIF(D2,TODAY(),"Y")<10,MIN(40,F2),80))
--------------------


Example: 3/21/05 is 30.06 hours. I want the vacation time cell to
reflect 30.06, not 40 hours.

When I use this formula:
Code:
--------------------
=IF(DATEDIF(DATE(YEAR(D2),1,1),TODAY(),"y")<10,DATEDIF(D2,TODAY(),"m")*3.34,DATEDIF(D2,DATE(YEAR(E2) +9,12,31),"m")*3.34+DATEDIF(DATE(YEAR(E2)+10,1,1),TODAY(),"m")*6.67)
--------------------

my above formula will not work since anything over 10 years returns a
0.

Any ideas around this?

Thanks again!!!!!!!!!!!!!!!!!!!!!!!!!!