Vacation Days Accrued

January 1st.

Thanks! :-)

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!

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.

=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 I just changing it to "make it work" or is this a logical approach that will work in all scenerio's?

Thanks,
jp

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
>

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.

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

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

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