Tough Problem 5 (formula)

[barry houdini]

Pgc asked me to set the latest “tough problem”. I tried to come up with something that wouldn’t be too tough, but might stretch some of you a little.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
If you want to post comments or ideas, rather than fully-formed solutions, please feel free, all contributions are welcome.<o></o>
<o> </o>
The problem<o></o>
<o> </o>
A1 contains an employee "hire date". Employees accrue hours on their anniversary date each month based on the following<o></o>
<o> </o>
0 years – 0 hrs per month<o></o>
2 years – 3 hrs per month<o></o>
5 years – 7 hrs per month<o></o>
8 years – 10 hrs per month<o></o>
15 years+ – 20 hrs per month<o></o>
<o> </o>
You need to supply a single formula which will calculate the hours accrued so far this calendar year, you can assume that today’s date is in T1.<o></o>
<o> </o>
If the hire date is a date like 31<SUP>st </SUP>May then, for months with less than 31 days, the hours will accrue on the last day of that month. Employees earn their first hours on their 2<SUP>nd</SUP> anniversary.<o></o>
<o> </o>
Example<o></o>
<o> </o>
Hire date <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:date Month="6" Day="30" Year="2004">30th June 2004</st1:date> <o></o>
<o> </o>
This employee's first hours will accrue on <st1:date Month="6" Day="30" Year="2006">30<SUP>th</SUP> June 2006</st1:date>. <o></o>
<o> </o>
During 2009 the employee will have accrued 0 hours by today (20<SUP>th</SUP> January) but will then earn 3 hours per month on the 30<SUP>th</SUP> of each month (or 28<SUP>th</SUP> February because that month has no 30th), until 30th June, which is the 5 year anniversary, so hours earned on that day, and every subsequent 30th in 2009, will be 7.<o></o>
<o> </o>
So if today was <st1:date Month="4" Day="1" Year="2009">1<SUP>st</SUP> April 2009</st1:date> then the formula should give the answer 9 (3+3+3), but if today was 3<st1:date Month="12" Day="1" Year="2009">1<SUP>st</SUP> December 2009</st1:date> the formula will give 64 (3+3+3+3+3+7+7+7+7+7+7+7). Of course the result will revert to zero on <st1:date Month="1" Day="1" Year="2010">1<SUP>st</SUP> January 2010</st1:date><o></o>
<o> </o>
As with previous problems, if you are an MVP, or if you found this simple, then please wait 24 hours before you post to give others a chance.<o></o>
<o> </o>
Thank you for reading<o></o>
<o> </o>
Regards, barry<o></o>

 

[schielrn]

One question I have at the moment.

If there start date is June 20th 2004. On June 30th 2009 for that month, I assume they would accrue 7 hours? I have figured it out to accrue 3 hours, but I think 7 would be accurate and wanted to verify that is what you were looking for?

 

[Lewiy]

Ok, this is quite a tricky one, but I think I have a solution. It requires the start date in A1 and the current date in A2 (for some reason when I changed all the A2's to TODAY() or NOW() I hit an error.....perhaps someone can clean that up for me). Anyway, here's my solution:

=SUMPRODUCT(--(LOOKUP(DATEDIF($A$1,DATE(YEAR(A2),ROW(INDIRECT("A1:A"&((MONTH(A2)-1)+IF(DAY($A$1)<=DAY(A2),1,0)))),DAY(A2)),"m"),{0,12,60,96,180},{0,3,7,10,20})))

 

[schielrn]

Lewiy,

I have just notices a couple issues with that.

1. I believe 12 should be 24 in your lookup.
2. It doesn't like dates from 1/1-1/29 of any year, gives a #REF error.
3. For June 30, 2006 it should be 3 and it calculates to 18.

Mine is far from done and I am to the part where I need to split the years. If everyone started on Jan 1 - Jan 31st I would be fine. Maybe I would just require the company to start all hires in January and NO EXCEPTIONS!!! Here is what I have started to work with:

=LOOKUP(SUMPRODUCT(--(ROW(INDIRECT(A1&":"&T1))=EOMONTH(ROW(INDIRECT(A1&":"&T1)),0)))-1,{0,24,60,96,180},{0,3,7,10,20})*SUMPRODUCT((ROW(INDIRECT(A1&":"&T1))=EOMONTH(ROW(INDIRECT(A1&":"&T1)),0))*(YEAR(ROW(INDIRECT(A1&":"&T1)))=YEAR(T1))*(ROW(INDIRECT(A1&":"&T1))>EOMONTH(DATE(YEAR(A1)+2,MONTH(A1),DAY(A1)),0)))

A1 is the start date and T1 is the input date to calculate based off of. Also this is more expensive than yours. After posting mine and looking at it on the board, man that is ugly and hard to follow.

 

[Lewiy]

Lewiy,

I have just notices a couple issues with that.

1. I believe 12 should be 24 in your lookup.
2. It doesn't like dates from 1/1-1/29 of any year, gives a #REF error.
3. For June 30, 2006 it should be 3 and it calculates to 18.
on the board, man that is ugly and hard to follow.

Schliern:
1. Yes, good catch, changing 12 to 24 also corrects the problem with number 3.
2. The Ref error indicates no hours accrued, if you are starting on the 30th of the month then 1-29 of Jan will always be zero. Tried to put some error handling in, but I think I have nesting issues.
3. See 1!

 

[schielrn]

Very nice formula! I actually understand it after walking through the evaluate feature, much better than my long drawn out formula.

 

[barry houdini]

One question I have at the moment.

If there start date is June 20th 2004. On June 30th 2009 for that month, I assume they would accrue 7 hours? I have figured it out to accrue 3 hours, but I think 7 would be accurate and wanted to verify that is what you were looking for?

Hello schielrn,

The accrual always takes place on the anniversary date (or the last day of the month as decribed previously) so 7 hours would accrue on 20th June 2009 if the hire date was 20th June 2004

 

[barry houdini]

Lewiy, nice approach

Ideally the formula will return 0 rather than #REF! for the early days of the year........

I get an incorrect result for 28th Feb 2009 if the hire date is 30th June 2004. 3 days will accrue on 31st January 2009 and 3 days will also accrue on 28th February 2009 because there is no 30th in that month, obviously, so the result should be 6 for 28th February, not 3

You can't replace A2 with TODAY() because that breaks the nested function limit.......but a cell reference for TODAY is fine....

Finally.......you don't need the -- because LOOKUP returns numeric results

 

[Lewiy]

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Ideally the formula will return 0 rather than #REF! for the early days of the year........<o></o>

<o></o>
Agreed, but as mentioned I now can’t add any error handling due to the nesting (unless I use XL2007!!)<o></o>

<o></o>

I get an incorrect result for 28th Feb 2009 if the hire date is 30th June 2004. 3 days will accrue on 31st January 2009 and 3 days will also accrue on 28th February 2009 because there is no 30th in that month, obviously, so the result should be 6 for 28th February, not 3<o></o>

<o></o>
I’ve noticed a small number of dates that it doesn’t work for, specifically around the 2 year mark……why are these things never simple!!!<o></o>
<o></o>

<o></o>

Finally.......you don't need the -- because LOOKUP returns numeric results

<o></o>
<o></o>
Good point….I’ll take that out <o></o>
<o></o>
I’d love to see a full working solution to this as it’s had my brain frying for several hours now!!! <o></o>

 

[pgc01]

Hi

It feels good to be able to participate! This is my first attempt. I somehow feel I could make it simpler.

In A1 the hire date. Using T1 as today's date, for tests:

=SUMPRODUCT(LOOKUP(((ROW(1:12)<MONTH(T1))+(ROW(1:12)=MONTH(T1))*(((DAY(T1)>=DAY(A1))+(DAY(T1+1)=1))>0))*((YEAR(T1)-YEAR(A1)+1)*12-MONTH(A1)+ROW(1:12)),{0,0;36,3;72,7;96,10;196,20}))

For the real formula replace T1 with Today()