Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vacation accrual formula | Excel Discussion (Misc queries) | |||
Vacation accrual Spreadsheet | Excel Discussion (Misc queries) | |||
Vacation/Sick accrual | Excel Worksheet Functions | |||
Please help!! Vacation Accrual Formula | Excel Worksheet Functions | |||
Vacation Accrual Formula | Excel Worksheet Functions |