Add credit based on hire date

[jei eusebio]

Hi Ninjas,

I'm trying to create a file that would add 1 leave credit per month based on an agents hire date, but it seems that what I did is wrong because it adds 1 credit even though the current date is not the same as the persons hire date.

Attached a file for reference.

Thanks in advance guys!

 

[eibi]

JE,

Your Original Formula:

=IF(YEAR(TODAY())<2016,0,IF(YEAR(VLOOKUP($C4,G:H,2,0))<2016,MONTH(TODAY()),MONTH(TODAY())-MONTH(VLOOKUP($C4,G:H,2,0))))

This formula is returning MONTH(TODAY()) -- which happens to be the numeral that represents today's month (January). That's why your formula is returning a value of "1".

What do you think of this formula instead:

=IF(YEAR(TODAY())<2016,0,IF(YEAR(VLOOKUP($C4,G:H,2,0))<2016,((YEAR(TODAY())-YEAR(VLOOKUP($C4,G:H,2,0)))*12)+MONTH(TODAY())-MONTH(VLOOKUP($C4,G:H,2,0)),MONTH(TODAY())))

See attached.

I'm not sure exactly what output you're looking for...

 

[jei eusebio]

Hi Eibi,

Thanks for your reply. Based on the sample date you used, the Agent should not any available credits yet since his start date was 11/24, so basically he would only earn a leave credit every 24th of the month.

 

[eibi]

Your original post indicates that the Agent earns 1 credit per month -- and the test date provided in your sample file is 11/24/2014 (which was 14 months ago...)

I assumed that there should be 14 available credits.

Have I misunderstood your intent?

 

[jei eusebio]

I might have not explained it clearly .. lol ..

Because every year leave credits are refreshed so they would only earn a credit when they reach the same day as their hire date .. like in the sample data, hire date is 11/24, so Agent1 would earn his 1st leave credit on the Jan 24th ..

^_^

 

[eibi]

Test this one...

=IF(YEAR(TODAY())<2016,0,IF(YEAR(VLOOKUP($C4,G:H,2,0))<2016,IF(DAY(TODAY())<VLOOKUP($C4,G:H,2,0)-EOMONTH(VLOOKUP($C4,G:H,2,0),-1),MONTH(TODAY())-1,MONTH(TODAY())),0))

 

[jei eusebio]

It worked! Thanks a lot Eibi! :D