Solved: Working With Dates

[zoom38]

Im trying to create a formula that will test to see if a date falls between two other dates and if true a certain amount of hours of leave is credited.
This is what I have so far.

IF(AND(AND(($J$2>=MIN($A8,$A8+13)),$J$2<=MAX($A8,$A8+13)),(DATEDIF($J$2,$A8+13,"m")=24))=TRUE, 24,IF(AND(AND(($J$2>=MIN($A9,$A9+13)),$J$2<=MAX($A9,$A9+13)),(DATEDIF($J$2,$A9+13,"m")=36))=TRUE,24,0))

In J2 is the employees hire date. A8 is the beginning of a pay period and A8 +13 is the end of the pay period. Using a Min Max Pearson formula Im testing to see if j2 falls withing the period A8 and A8 +13. The problem lies when J2 and A8 are in different years. J2 stays firm but A8 changes each year. I have this set for 26 pay periods per year , A9, A10 etc. I then used datedif to test how many months are between J2 and a8 + 13. If the months are = to 24 and j2 falls between a8 and a8+13 then hours of leave credited would be 24. The same for 36 months. At 48 months the leave increases to 36.

I have done alot of searching on the net and I can't come up with a solution. I'm hoping that you guys may be able to come up with something.

Thanks
Gary

[Ismael]

Hi can you post a example.

Regards,

Ismael

[XLGibbs]

It think a slight restructuring may be needed, but there is a more efficient way to get the desired result...

J2 is hire date, and A8 is beginning of a pay period...

((((A8-J2)+13))/360)*12


For a hire date of 1/1/2005 and a pay period beginning 2/1/2006
the resulting calculation is 13.63 months

So you can simply use this calculation....

LOOKUP(((((A8-J2)+13))/360)*12,{0,24,36,48})

which produces the number of hours credited based on the criteria you indicated ...

[zoom38]

I zipped and attached the file. In cell J2 is the employee hire date. In A8 starts the pay weeks (fiscal year April 1 - March 31). In column V there is the hours earned for personal leave. In order to get it to work correctly I had to create a reference table in columns AT thru AY. In those columns I have the cells calculating the earned leave thru 30 years of service. What I was trying to do was create a formula that would eliminate the reference table. You see in column AR that there are only 4 levels of earned leave (0, 24, 32 & 40).
What I created seems to work but I wanted to streamline it and make it more universal. I also had to do this with vacation leave but I thought that was the only way to do it, there is more to it than the personal leave.

Please take a look, if it can be stremlined that would be great.

Thanks
Gary

[zoom38]

Disregard my last post, I am going to give XL's suggestion a try. I let you know how it goes.

Thanks

[XLGibbs]

zoom, I mis read the post....One adjustment

IF(((((A8-J2)+13))/360)*12>=48,36,IF(((((A8-J2)+13))/360)*12>=24,24,0))

may be off on the parenthesis...I didn't realize that it says 24 for 36 months....this should do what you need...

If the calculation >= 48 then hours is 36, if not, it checks to see if it is >= 24, where it would be 24. Otherwise, zero.

Hope that helps..

[XLGibbs]

Some excellent information in an article by Cyberdude....may be useful as well..

Dates and Times (http://www.vbaexpress.com/forum/articles.php?action=viewarticle&artid=61)

[zoom38]

XL I can't get it. Maybe im a little thick on this one. I used your latest formula but I still cant get it to only give the leave when the date in J2 falls within the interval in A8 & A8+13. Im talking about an aniversary date. If J2 has the date 04/12/2002 and A8 is 04/03/2005 and A8 + 13 = 04/16/2005, the earned leave would be 24 hrs because the anniversary date falls within that interval. But in the next pay period, if A9 is 04/17/2005 and A9 +13 is 04/30/2005 the earned leave should be zero because the anniversary date does not fall within A9 and A9 +13. This is why I used the following CPearson formula:

=AND((TDate>=MIN(TDate1,TDate2)),TDate<=MAX(TDate1,TDate2))
=AND((J2>=MIN(A8,A8+13)),J2<=MAX(A8,A8+13))

This formula is great with one exception, I can't get it to work when J2 and A8 are in different years.

I also have to admit that I don't have a clue what is happening with your Lookup formula which I tried but it always gives me a value other than 0 when the anniversary date does not fall within the interval A8 & A8 +13.

I have 26 payperiods A8 thru A33. The leave should be 0 in all of the pay periods except the payperiod in which the anniversary date falls. In a previous post above I attached the file if you need to look to understand what I am talking about.

I also looked over cyberdudes article on dates but I didn 't find anything on date intervals and anniversary dates.

Thanks Again
Gary

[XLGibbs]

Okay...I see the dilemma now....

This one works....

=IF(AND(MONTH(J2)>=MONTH(A8),MONTH(J2)<=MONTH(A8+13)),LOOKUP(((((A8-J2)+13))/360)*12,{0,24,36,48},{0,24,24,36}),0)

I can't download or upload from here at work, but this is tested as written...

[zoom38]

XL your last formula doesn't work correctly either.

J2 = 04/12/2002 - Hire Date
A8 = 03/24/2005, A8+13 = 04/06/2005 - 1st pay period
A9 = 04/07/2005, A9+13 = 04/20/2005 - 2nd pay period
A10 = 04/21/2005, A10+13 = 05/04/2005 - 3rd pay period
A11 = 05/05/2005, A11+13 = 05/18/2005 - 4th pay period

The following are the formulas used:

=IF(AND(MONTH($J$2)>=MONTH($A8),MONTH($J$2)<=MONTH($A8+13)),LOOKUP((((($A8-$J$2)+13))/365)*12,{0,24,36,48},{0,24,24,36}),0)
Result is 24, incorrect, the hire date anniversary does not fall within this interval, result should be 0.

=IF(AND(MONTH($J$2)>=MONTH($A9),MONTH($J$2)<=MONTH($A9+13)),LOOKUP((((($A9-$J$2)+13))/365)*12,{0,24,36,48},{0,24,24,36}),0)
Result is 24, Correct, the hire date anniversary falls within this interval.

=IF(AND(MONTH($J$2)>=MONTH($A10),MONTH($J$2)<=MONTH($A10+13)),LOOKUP((((($A10-$J$2)+13))/365)*12,{0,24,36,48},{0,24,24,36}),0)
Result is 24, incorrect, the hire date anniversary does not fall within this interval, result should be 0.

=IF(AND(MONTH($J$2)>=MONTH($A11),MONTH($J$2)<=MONTH($A11+13)),LOOKUP((((($A11-$J$2)+13))/365)*12,{0,24,36,48},{0,24,24,36}),0)
Result is 0, correct, the hire date does anniversary does not fall within this interval.

I believe this occurs because the Month function gives the amount of full months between the dates and in A8, A9 and A10 fall within the same number of full months.

With all of the datefunctions that Excel has I cant believe one of them could not do this. I've tried them all and can't figure it out.

Thanks
Gary

[XLGibbs]

hey at least I am trying...give me a few minutes, there are more ways to skin this kitty.

[XLGibbs]

Okay, this one seems to do the trick...

=IF(AND(TEXT($J$2,"mmdd")>=TEXT(A8,"mmdd"),TEXT($J$2,"mmdd")<TEXT(A9,"mmdd")),LOOKUP(((((A8-$J$2)+13))/360)*12,{0,24,36,48},{0,24,24,36}),0)

Dragged down the column..I took a peek at your document finally....this works...

Basically compares the text equivalent of only the month and day. If the beginning of the pay period is >= hire date, but < next pay period, then do the lookup.

The lookup works like this:

(A8-$J$2)+13))/360)*12

this converts the equivaelent end of the payperiod less the hire date to months...

that value then is looked up in the array of
{0,24,36,48}
where it will find the closest match <= to the number
based on the position in the array, the result wouldbe :
{0,24,24,36}

the same as having a vlookup table like this:
months hours
0 0
24 24
36 24
48 36

sample attached.

[zoom38]

XL, your last formula is amazing and appears to work for all scenarios. Thank you very much. Another one solved.

Gary

[zoom38]

XL, one quick question, why do you use 360 instead of 365?

[XLGibbs]

Just so it averages clean to 30 days per month...the main function in my formula is determining the months by subtracting the dates which gives days as integer, thus / 360 for the years, * 12 for months...I suppose 365 would work as well, but since you are 26 week pay periods, as opposed to biweekly, the straightline 360 seemed cleaner.

Glad it worked out...