1. ## Help with a formula please (Office 2000)

Hello,
I can't seem to come up with a (IF) formula that would return the information I need. I'm trying to do a employee benefits spreadsheet in which it calculates earned vacation hours.

Our employees do not accrue any benefits until after their third month. (Correction, it does accrue but it is not available until after their third month.) After that point, they accrue 6.67 hours per month until the end of their first year. The following year, they accrue 9.34 hrs a month. Time is carried over to the first quarter of the following year only.

Can anyone help me with this formula? I've tried the (IF) and (TODAY or NOW) but I am not very successful.

Thanks!

2. ## Re: Help with a formula please (Office 2000)

If the starting date is in cell A1, does this formula work?

=(DATEDIF(A1,TODAY(),"m")>3)*(DATEDIF(A1,MIN(DATE( YEAR(A1),12,31),TODAY()),"m")*6.67+MAX(0,(DATEDIF( A1,TODAY(),"m")-DATEDIF(A1,MIN(DATE(YEAR(A1),12,31),TODAY()),"m")) )*9.34)

If not could you give some additional info what dates this fails with and what the correct numbers should be.

Steve

3. ## Re: Help with a formula please (Office 2000)

Hi Steve, Thanks for the reply.

Your formula doens't seem to work for me for the employees that have a hire date greater than a year ago. I probably didn't explain the problem very well.

If an employee has been with us for less than two years, they earn two weeks vacation (80 hours or 6.67 hrs per month) It is not available however to them until after the third month. (Probation period) After two years, they earn 14 days (112 hours or 9.34 hrs per month). I'm not too concerned about the roll-over issue. I can take care of that some other way.

Using your formula, some employees results are well over the 400 hour mark. Particularly those who were hired in 2000.

Does this explaination make it clearer?

Thanks again,

4. ## Re: Help with a formula please (Office 2000)

<P ID="edit" class=small>(Edited by JohnBF on 08-Nov-04 14:28. Changed the part starting with MOD.)</P>Louise, try

=CHOOSE(1+(DATEDIF(A1,TODAY(),"m")>3)+(DATEDIF(A1, TODAY(),"m")>12),0,6.67,9.34)*MOD(DATEDIF(A1,TODAY (),"m"),12)

(And if it works, thank Steve for breaking the gound, but I'm not quite sure if it fits your problem.)

5. ## Re: Help with a formula please (Office 2000)

Hi John,
That works perfectly! Thank you both!

Now I'll have to somehow disect the formula so that I can understand what it means now and in the future. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

Thanks again,

6. ## Re: Help with a formula please (Office 2000)

Hi John,

Ok, I give up. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> I read about the "CHOOSE" function and then I tried looking for "DATEDIF" in the help files but nothing came up. Can you tell me what that is and also what the "m" stands for?

Thank you very much!

8. ## Re: Help with a formula please (Office 2000)

Excel 2000 was the first version of Excel where =DATEDIF(startdate, enddate, interval) was covered in Help; for a great explanation see Chip Pearson on DATEDIF. In this case, the "m" argument is setting an interval of "month" to calculate the number of months between the two dates.

=CHOOSE(argument, result1, result2, etc...) should also be documented in Help, though I'm using it a little unconventionally. CHOOSE selects the result according to the number 1,2,3,etc. returned by 'argument'. In this case I'm using 1 as the default, and if the period of employment is more than 3 months, another one gets added, and if over 12 months another 1 gets added. So the result is either 1, 2, or 3, which is used to select the vacation accrual (earning) factor of 0 (zero), 6.67, or 9.34.

The MOD argument takes the number of months elapsed since the employees start date, and then strips out all the periods of 12 full months, leaving only the most recent count of months up to 12.

So, CHOOSE selects the applicable vacation accrual rate, and the MOD part figures the months earned since the most recent anniversary, and they are multiplied.

HTH.

9. ## Re: Help with a formula please (Office 2000)

Thank you both very much!

10. ## Re: Help with a formula please (Office 2000)

No fair writing short answers while I'm composing "War and Peace"! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

11. ## Re: Help with a formula please (Office 2000)

Oh but I enjoyed the novel! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

12. ## Re: Help with a formula please (Office 2000)

Sorry...I would have written more if I would have known more <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

- Brett

13. ## Re: Help with a formula please (Office 2000)

Hello again,
I guess the formula doesn't work as I thought it would after all. I just finished setting up the new spreadsheets for 2005 and when I looked at the "earned" time for this year, (It's only January so it should only be one month) it calculated it for the entire year instead of just for January on those who have been with us more than two years, and on those that were hired in 2004, it calculated the hours from the day they started.

What adjustment should I make to the formula for it to calculate the year to date earned for this year only. This should work for years to come as well.

Thanks again and Happy New Year!

14. ## Re: Help with a formula please (Office 2000)

The formula last written calculates the vacation earnings since the most recent anniversary of hire date, ans seems to be working for me - except for one detail, you seem to be treating this month as already over, the formula doesn't; someone hired 6/1/2002 is calculated to have earned 7 months since most recent anniversary of hire date. can you post a sample to show what you mean?

P.S., this should include the current month in the accrual calculation since last anniversary:

=CHOOSE(1+(DATEDIF(A1,TODAY(),"m")>3)+(DATEDIF(A1, TODAY(),"m")>12),0,6.67,9.34)*MOD(DATEDIF(A1,DATE( YEAR(TODAY()),MONTH(TODAY())+1,1),"m"),12)

15. ## Re: Help with a formula please (Office 2000)

Hi John,

It looks like I wasn't very clear in my original post. Sorry.

If an employee has been with us for less than two years, they earn two weeks vacation (80 hours or 6.67 hrs per month) It is not available to them however, until after the third month. (Probation period) So, if someone was hired on 6/1/04, they would have earned, after 90 days, 20.01 hours (6.67/month). An additional 6.67 per month afterwards until the end of the year. Starting a new year, the earning starts all over again at 6.67hrs a month.

After two years of employment, they earn 14 days (112 hours or 9.34 hrs per month). The same rules apply as far as starting over at the start of the year.
It doesn't matter if the formula shows a progression during the month as long as at month-end the cumulative total equals 6.67 or 9.34 /month respectively.

I think the only thing missing with the formula is the starting over factor. Can this be done? If it's still not clear, please let me know.

Thanks again,

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•