Results 1 to 15 of 38

20041108, 19:03 #1
 Join Date
 Jul 2001
 Location
 Florida, USA
 Posts
 394
 Thanks
 0
 Thanked 0 Times in 0 Posts
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!Louise

20041108, 20:06 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20041108, 20:45 #3
 Join Date
 Jul 2001
 Location
 Florida, USA
 Posts
 394
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 rollover 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,Louise

20041108, 21:28 #4
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Help with a formula please (Office 2000)
<P ID="edit" class=small>(Edited by JohnBF on 08Nov04 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.)John ... I float in liquid gardens
UTC 7ąDS

20041108, 21:36 #5
 Join Date
 Jul 2001
 Location
 Florida, USA
 Posts
 394
 Thanks
 0
 Thanked 0 Times in 0 Posts
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,Louise

20041108, 23:01 #6
 Join Date
 Jul 2001
 Location
 Florida, USA
 Posts
 394
 Thanks
 0
 Thanked 0 Times in 0 Posts
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!Louise

20041108, 23:08 #7
 Join Date
 Jan 2001
 Location
 Ankeny, Iowa, USA
 Posts
 298
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with a formula please (Office 2000)
<!post=This ,371329>This <!/post>thread leads you to an explanation of datedif

20041108, 23:14 #8
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
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.John ... I float in liquid gardens
UTC 7ąDS

20041108, 23:21 #9
 Join Date
 Jul 2001
 Location
 Florida, USA
 Posts
 394
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with a formula please (Office 2000)
Thank you both very much!
Louise

20041108, 23:22 #10
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
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>
John ... I float in liquid gardens
UTC 7ąDS

20041108, 23:37 #11
 Join Date
 Jul 2001
 Location
 Florida, USA
 Posts
 394
 Thanks
 0
 Thanked 0 Times in 0 Posts
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>
Louise

20041109, 02:19 #12
 Join Date
 Jan 2001
 Location
 Ankeny, Iowa, USA
 Posts
 298
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20050103, 18:12 #13
 Join Date
 Jul 2001
 Location
 Florida, USA
 Posts
 394
 Thanks
 0
 Thanked 0 Times in 0 Posts
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!Louise

20050103, 19:16 #14
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
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)John ... I float in liquid gardens
UTC 7ąDS

20050104, 02:45 #15
 Join Date
 Jul 2001
 Location
 Florida, USA
 Posts
 394
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 monthend 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,Louise