# Tough Problem 5 (formula)



## barry houdini (Jan 20, 2009)

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 (Jan 20, 2009)

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 (Jan 20, 2009)

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 (Jan 20, 2009)

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 (Jan 20, 2009)

schielrn said:


> Lewiy,
> 
> I have just notices a couple issues with that.
> 
> ...


 
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 (Jan 20, 2009)

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


----------



## barry houdini (Jan 20, 2009)

schielrn said:


> 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 (Jan 20, 2009)

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 (Jan 21, 2009)

barry houdini said:


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





barry houdini said:


> 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 (Jan 21, 2009)

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


----------



## pgc01 (Jan 21, 2009)

This is an equivalent formula, I just did a small change, but I think it makes more sense like this:

=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))*12-MONTH(A1)+ROW(1:12)),{-12,0;24,3;60,7;84,10;180,20}))


----------



## Andrew Fergus (Jan 21, 2009)

Hi Pedro

With a start date of 30 June 2004 and an 'as at' date of 28 Feb 2009, your two different formulas return different results - I believe the second formula is correct with 6 hours.

Also, try a start date of 31 July 2004 and an 'as at' date of 30 June 2012 and check your result.

Andrew


----------



## pgc01 (Jan 21, 2009)

Hi Andrew

Thanks for looking into the formulas.



> With a start date of 30 June 2004 and an 'as at' date of 28 Feb 2009, your two different formulas return different results - I believe the second formula is correct with 6 hours.


 
I tried and got the same result in both, 6 hours.(?)



> Also, try a start date of 31 July 2004 and an 'as at' date of 30 June 2012 and check your result.


 
That was wrong. I had read in the table 7 years (84 months) when it is, in fact 8 years (96 months) (in the other formula it should be 108).

This is the formula with the correction:

=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))*12-MONTH(A1)+ROW(1:12)),{-12,0;24,3;60,7;*96*,10;180,20}))<?XML:NAMESPACE PREFIX = MONTH(T1))+(ROW(1 /><MONTH(T1))+(ROW(1:12)=MONTH(T1))*(((DAY(T1)><MONTH(T1))+(ROW(1:12)=MONTH(T1))*(((DAY(T1)>

Thank you again
Pedro
</MONTH(T1))+(ROW(1:12)=MONTH(T1))*(((DAY(T1)>
</MONTH(T1))+(ROW(1:12)=MONTH(T1))*(((DAY(T1)>


----------



## barry houdini (Jan 23, 2009)

Thank you Pedro, that seems to return exactly the results I expected in all scenarios (unless anybody knows any different ), although for robustness might it be better to replace ROW(1:12) with ROW(INDIRECT("1:12"))?

I have a version of my own which uses DATEDIF like Lewiy's. It works but I'm just trying to see if I can shorten it......I'll post back soon.

Thanks to everybody who has contributed so far, any more?


----------



## pgc01 (Jan 23, 2009)

barry houdini said:


> for robustness might it be better to replace ROW(1:12) with ROW(INDIRECT("1:12"))?


 
Yes, Barry, I agree. Another option is to use directly the array {1,2...,12}. This way the formula would not be volatile. Or use this array inside a name.


----------



## barry houdini (Jan 29, 2009)

Sorry for the delay, I know many of you were waiting with bated breath 

Initially I thought that this formula was best

=IF(MONTH(T$1)*(DAY(A1)>DAY(T$1))=1,0,SUMPRODUCT(LOOKUP(ROW(INDIRECT(DATEDIF(A1,DATE(YEAR(T$1),1,0),"m")&":"&DATEDIF(A1+1,T$1+1,"m")-1))+1,{0,24,60,96,180;0,3,7,10,20})))

I wanted to remove the IF function but couldn't find a way......

But I noticed that the formula gave incorrect results in some rare cases, e.g. when A1 was 28th February (in a non leap year) and T1 was a date like 29th April 2009.

....so kudos to pgc for coming up with the best formula, in my opinion.


----------



## Andrew Fergus (Jan 29, 2009)

barry houdini said:


> ....so kudos to pgc for coming up with the best formula, in my opinion.


I agree - try as I might I couldn't break Pedro's formula....


----------

