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):

Please Login or Register  to view this content.

Column 2 Formula (>=10 Years):

Please Login or Register  to view this content.

Column 3 Formula (Converts Column 1 OR 2 into 40 OR 80 hours):

Please Login or Register  to view this content.

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" <jprogrammer.20cjam_1135095001.272@excelforum-nospam.com> wrote in message
news:jprogrammer.20cjam_1135095001.272@excelforum-nospam.com...
>
> 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!
>
>
> --
> jprogrammer
> ------------------------------------------------------------------------
> jprogrammer's Profile: http://www.excelforum.com/member.php...o&userid=29781
> View this thread: http://www.excelforum.com/showthread...hreadid=494904
>

[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. In the formula:

Please Login or Register  to view this content.

What does the 400 represent?

Thanks!

[Cutter]

Just a guess on my part:

40 hrs per year for the first 10 years?

[naiveprogrammer]

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

"Bernie Deitrick" wrote:

> 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" <jprogrammer.20cjam_1135095001.272@excelforum-nospam.com> wrote in message
> news:jprogrammer.20cjam_1135095001.272@excelforum-nospam.com...
> >
> > 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!
> >
> >
> > --
> > jprogrammer
> > ------------------------------------------------------------------------
> > jprogrammer's Profile: http://www.excelforum.com/member.php...o&userid=29781
> > View this thread: http://www.excelforum.com/showthread...hreadid=494904
> >
>
>
>

[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



"naiveprogrammer" <naiveprogrammer@discussions.microsoft.com> wrote in
message news:32CAE5B9-8451-48FF-BB33-99E1428C1C73@microsoft.com...
> 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!!!
>
> "Bernie Deitrick" wrote:
>
>> 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" <jprogrammer.20cjam_1135095001.272@excelforum-nospam.com>
>> wrote in message
>> news:jprogrammer.20cjam_1135095001.272@excelforum-nospam.com...
>> >
>> > 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!
>> >
>> >
>> > --
>> > jprogrammer
>> > ------------------------------------------------------------------------
>> > jprogrammer's Profile:
>> > http://www.excelforum.com/member.php...o&userid=29781
>> > View this thread:
>> > http://www.excelforum.com/showthread...hreadid=494904
>> >
>>
>>
>>

[Bernie Deitrick]

Cutter,

Ding ding ding ding! We have a winner... you are correct. :-)

Bernie


"Cutter" <Cutter.20csbn_1135106701.1611@excelforum-nospam.com> wrote in
message news:Cutter.20csbn_1135106701.1611@excelforum-nospam.com...
>
> Just a guess on my part:
>
> 40 hrs per year for the first 10 years?
>
>
> --
> Cutter
> ------------------------------------------------------------------------
> Cutter's Profile:
> http://www.excelforum.com/member.php...fo&userid=9848
> View this thread: http://www.excelforum.com/showthread...hreadid=494904
>

[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 this formula is doing now. Works great!

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

Thanks!

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

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

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

Yesterday, 07:15 PM
Bernie Deitrick Posts: n/a

Re: Vacation Days Accrued

--------------------------------------------------------------------------------

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]

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" <jprogrammer.20e8em_1135174201.1603@excelforum-nospam.com> wrote in message
news:jprogrammer.20e8em_1135174201.1603@excelforum-nospam.com...
>
> 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 this
> formula is doing now. Works great!
>
> I have a similar worksheet that is for office employees only. So the
> criteria is a little different. You've already helped me with the
> first bullet below but how could I incorporate the following 2 bullets
> with the first bullet? See below:
>
> Thanks!
>
>> *During first calendar year vacation will accrue at 3.34 hours per
>> complete month of service.
>>
>> *Beginning January 1 following the date of hire- employee will receive
>> 80 hours each year.
>>
>> *Beginning January 1 of tenth calendar year of service- employee will
>> receive 120 hours each year.
>
>> Yesterday, 07:15 PM
>> Bernie Deitrick Posts: n/a
>>
>> Re: Vacation Days Accrued
>>
>> --------------------------------------------------------------------------------
>>
>> 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
>>
>>
>
>
> --
> jprogrammer
> ------------------------------------------------------------------------
> jprogrammer's Profile: http://www.excelforum.com/member.php...o&userid=29781
> View this thread: http://www.excelforum.com/showthread...hreadid=494904
>

[jprogrammer]

The criteria that I gave this morning is different than the criteria 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 per
completed month of service. (40.08 hours)


*Beginning January 1 following the date of hire- employee will receive 80 hours each year. (80 is a flat rate)

*Beginning January 1 of tenth calendar year of service- employee will receive 120 hours each year. (120 is a flat rate)

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 is returning 1747.34. Is this what you were talking about in your 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 including people that have been with the company for 10 years or more.

Please Login or Register  to view this content.

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

Thanks,
jp

[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" <jprogrammer.20ebvm_1135178700.9671@excelforum-nospam.com> wrote in message
news:jprogrammer.20ebvm_1135178700.9671@excelforum-nospam.com...
>
> Hire Date: 02/09/1979 should be 2147.74 hours, yet in the formula is
> returning 1747.34. Is this what you were talking about in your
> previous post?
>
> Sorry to bug you so much with this! I REALLY appriciate your help!
>
>
> --
> jprogrammer
> ------------------------------------------------------------------------
> jprogrammer's Profile: http://www.excelforum.com/member.php...o&userid=29781
> View this thread: http://www.excelforum.com/showthread...hreadid=494904
>

[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 years or greater, the formula returns a 0 rather than the actual time 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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Any ideas around this?

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