Vacation Accrual
 

I have read through the past posts and havent found exactly what I am looking
for. I need to have a formula that will tell me how many months an employee
has worked after his anniversary THIS year, up to his anniversary NEXT year.
I am able to make a formula that will calculate for this year, but when the
year changes, it messes up. EX. Ann. date of 10/1/08. On 11/1/08 it should
say "1", on 12/1/08 is should say "2", on 1/1/09 is should say "3", etc....

Vacation Accrual
 

Assuming F4 has your 10/1/08 BD, then this should work:

=INT((F7-$F$4)/(365/12))

F7 has the date in question. The result will return the number of whole
months since the anniversary. And the definition of a whole month is 365/12
or 30.4 days.

"Steve" wrote:

I have read through the past posts and havent found exactly what I am looking
for. I need to have a formula that will tell me how many months an employee
has worked after his anniversary THIS year, up to his anniversary NEXT year.
I am able to make a formula that will calculate for this year, but when the
year changes, it messes up. EX. Ann. date of 10/1/08. On 11/1/08 it should
say "1", on 12/1/08 is should say "2", on 1/1/09 is should say "3", etc....

Vacation Accrual
 

Thanks so much Mike. I am alittle confused (image that). I see that $F$4 is
the employees date of hire, what is f7? You said date in question, but I
guess I just don't understand.

Thanks so much!!

"Mike H." wrote:

Assuming F4 has your 10/1/08 BD, then this should work:

=INT((F7-$F$4)/(365/12))

F7 has the date in question. The result will return the number of whole
months since the anniversary. And the definition of a whole month is 365/12
or 30.4 days.

"Steve" wrote:

I have read through the past posts and havent found exactly what I am looking
for. I need to have a formula that will tell me how many months an employee
has worked after his anniversary THIS year, up to his anniversary NEXT year.
I am able to make a formula that will calculate for this year, but when the
year changes, it messes up. EX. Ann. date of 10/1/08. On 11/1/08 it should
say "1", on 12/1/08 is should say "2", on 1/1/09 is should say "3", etc....

Vacation Accrual
 

F7 would be today or whatever date you're trying to determine to compare
with. As I thought about this over teh weekend, I really don't think this
does exactly what you want it to, however. Let me think on it a little more.

"Steve" wrote:

Thanks so much Mike. I am alittle confused (image that). I see that $F$4 is
the employees date of hire, what is f7? You said date in question, but I
guess I just don't understand.

Thanks so much!!

"Mike H." wrote:

Assuming F4 has your 10/1/08 BD, then this should work:

=INT((F7-$F$4)/(365/12))

F7 has the date in question. The result will return the number of whole
months since the anniversary. And the definition of a whole month is 365/12
or 30.4 days.

"Steve" wrote:

I have read through the past posts and havent found exactly what I am looking
for. I need to have a formula that will tell me how many months an employee
has worked after his anniversary THIS year, up to his anniversary NEXT year.
I am able to make a formula that will calculate for this year, but when the
year changes, it messes up. EX. Ann. date of 10/1/08. On 11/1/08 it should
say "1", on 12/1/08 is should say "2", on 1/1/09 is should say "3", etc....

Vacation Accrual
 

Again Mike, thanks for the information. I have plugged it in, and it is not
giving me what I am looking for. It is giving me the total number of months
since their start date thru today. It is hard to describe what I am trying
to accomplish, but for each employee, I need a formula that will calculate
the number of months since their LAST anniversary, provided it is in that
calendar year, up to their next anniversary of the next calendar year? I
hope that makes sense...

"Mike H." wrote:

F7 would be today or whatever date you're trying to determine to compare
with. As I thought about this over teh weekend, I really don't think this
does exactly what you want it to, however. Let me think on it a little more.

"Steve" wrote:

Thanks so much Mike. I am alittle confused (image that). I see that $F$4 is
the employees date of hire, what is f7? You said date in question, but I
guess I just don't understand.

Thanks so much!!

"Mike H." wrote:

Assuming F4 has your 10/1/08 BD, then this should work:

=INT((F7-$F$4)/(365/12))

F7 has the date in question. The result will return the number of whole
months since the anniversary. And the definition of a whole month is 365/12
or 30.4 days.

"Steve" wrote:

I have read through the past posts and havent found exactly what I am looking
for. I need to have a formula that will tell me how many months an employee
has worked after his anniversary THIS year, up to his anniversary NEXT year.
I am able to make a formula that will calculate for this year, but when the
year changes, it messes up. EX. Ann. date of 10/1/08. On 11/1/08 it should
say "1", on 12/1/08 is should say "2", on 1/1/09 is should say "3", etc....

Vacation Accrual
 

This should do it:

=IF(MONTH(A4)-MONTH(NOW())<0,MONTH(NOW())-MONTH(A4)+IF(DAY(A4)DAY(NOW()),-1,0),"")

A4 is the birthdate you are evaluating and Now() is today (could substitute
a date for now() if you wish to evaluate on another date besides today.


HTH
"Steve" wrote:


Again Mike, thanks for the information. I have plugged it in, and it is not
giving me what I am looking for. It is giving me the total number of months
since their start date thru today. It is hard to describe what I am trying
to accomplish, but for each employee, I need a formula that will calculate
the number of months since their LAST anniversary, provided it is in that
calendar year, up to their next anniversary of the next calendar year? I
hope that makes sense...

"Mike H." wrote:

F7 would be today or whatever date you're trying to determine to compare
with. As I thought about this over teh weekend, I really don't think this
does exactly what you want it to, however. Let me think on it a little more.

"Steve" wrote:

Thanks so much Mike. I am alittle confused (image that). I see that $F$4 is
the employees date of hire, what is f7? You said date in question, but I
guess I just don't understand.

Thanks so much!!

"Mike H." wrote:

Assuming F4 has your 10/1/08 BD, then this should work:

=INT((F7-$F$4)/(365/12))

F7 has the date in question. The result will return the number of whole
months since the anniversary. And the definition of a whole month is 365/12
or 30.4 days.

"Steve" wrote:

I have read through the past posts and havent found exactly what I am looking
for. I need to have a formula that will tell me how many months an employee
has worked after his anniversary THIS year, up to his anniversary NEXT year.
I am able to make a formula that will calculate for this year, but when the
year changes, it messes up. EX. Ann. date of 10/1/08. On 11/1/08 it should
say "1", on 12/1/08 is should say "2", on 1/1/09 is should say "3", etc....

Vacation Accrual
 

I have plugged this in, and I believe it is working! Thanks so much for your
assistance!

"Mike H." wrote:

This should do it:

=IF(MONTH(A4)-MONTH(NOW())<0,MONTH(NOW())-MONTH(A4)+IF(DAY(A4)DAY(NOW()),-1,0),"")

A4 is the birthdate you are evaluating and Now() is today (could substitute
a date for now() if you wish to evaluate on another date besides today.


HTH
"Steve" wrote:


Again Mike, thanks for the information. I have plugged it in, and it is not
giving me what I am looking for. It is giving me the total number of months
since their start date thru today. It is hard to describe what I am trying
to accomplish, but for each employee, I need a formula that will calculate
the number of months since their LAST anniversary, provided it is in that
calendar year, up to their next anniversary of the next calendar year? I
hope that makes sense...

"Mike H." wrote:

F7 would be today or whatever date you're trying to determine to compare
with. As I thought about this over teh weekend, I really don't think this
does exactly what you want it to, however. Let me think on it a little more.

"Steve" wrote:

Thanks so much Mike. I am alittle confused (image that). I see that $F$4 is
the employees date of hire, what is f7? You said date in question, but I
guess I just don't understand.

Thanks so much!!

"Mike H." wrote:

Assuming F4 has your 10/1/08 BD, then this should work:

=INT((F7-$F$4)/(365/12))

F7 has the date in question. The result will return the number of whole
months since the anniversary. And the definition of a whole month is 365/12
or 30.4 days.

"Steve" wrote:

I have read through the past posts and havent found exactly what I am looking
for. I need to have a formula that will tell me how many months an employee
has worked after his anniversary THIS year, up to his anniversary NEXT year.
I am able to make a formula that will calculate for this year, but when the
year changes, it messes up. EX. Ann. date of 10/1/08. On 11/1/08 it should
say "1", on 12/1/08 is should say "2", on 1/1/09 is should say "3", etc....