| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating, filtering, and sorting 5 year anniversaries?
If I have an employee database with hire dates, and a company longevity award
program that gives an award for each 5th year anniversary, ie. 5, 10, 15, etc. years of service, what formula could I use to flag every record/row where an employee is entitled to an award, or not, in the coming 12 months? tia |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating, filtering, and sorting 5 year anniversaries?
hi
=datedif(hiredate,todaydate,"Y") it will return you the difference in completed years hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Ricter" escreveu: If I have an employee database with hire dates, and a company longevity award program that gives an award for each 5th year anniversary, ie. 5, 10, 15, etc. years of service, what formula could I use to flag every record/row where an employee is entitled to an award, or not, in the coming 12 months? tia |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating, filtering, and sorting 5 year anniversaries?
Gonna have to get a bit more complicated than that methinks. An employee
with a hire date of 1/1/2002 will result in 4 years of service, but they will be entitled to their 5th year award within the next 12 months... "Marcelo" wrote: hi =datedif(hiredate,todaydate,"Y") it will return you the difference in completed years hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Ricter" escreveu: If I have an employee database with hire dates, and a company longevity award program that gives an award for each 5th year anniversary, ie. 5, 10, 15, etc. years of service, what formula could I use to flag every record/row where an employee is entitled to an award, or not, in the coming 12 months? tia |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating, filtering, and sorting 5 year anniversaries?
Hi,
In my understanding you told that if the 5th year will be completed during the current year you should accrual (or something like that) the awards. right? the datedif function has variations that you can use as "Y" - Completed years "M" - Completed months "D" - Days "MD" - diference between the dates but months and years are ignorated "YD" - diference between the dates but days and years are ignorated hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Ricter" escreveu: Gonna have to get a bit more complicated than that methinks. An employee with a hire date of 1/1/2002 will result in 4 years of service, but they will be entitled to their 5th year award within the next 12 months... "Marcelo" wrote: hi =datedif(hiredate,todaydate,"Y") it will return you the difference in completed years hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Ricter" escreveu: If I have an employee database with hire dates, and a company longevity award program that gives an award for each 5th year anniversary, ie. 5, 10, 15, etc. years of service, what formula could I use to flag every record/row where an employee is entitled to an award, or not, in the coming 12 months? tia |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating, filtering, and sorting 5 year anniversaries?
No, I need to identify those anniversaries that will occur within the next 12
months, starting from today. I'm wondering now if I can "bracket" these dates by using your formula for one, and use a variation where I add 12 months for the other... "Marcelo" wrote: Hi, In my understanding you told that if the 5th year will be completed during the current year you should accrual (or something like that) the awards. right? the datedif function has variations that you can use as "Y" - Completed years "M" - Completed months "D" - Days "MD" - diference between the dates but months and years are ignorated "YD" - diference between the dates but days and years are ignorated hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Ricter" escreveu: Gonna have to get a bit more complicated than that methinks. An employee with a hire date of 1/1/2002 will result in 4 years of service, but they will be entitled to their 5th year award within the next 12 months... "Marcelo" wrote: hi =datedif(hiredate,todaydate,"Y") it will return you the difference in completed years hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Ricter" escreveu: If I have an employee database with hire dates, and a company longevity award program that gives an award for each 5th year anniversary, ie. 5, 10, 15, etc. years of service, what formula could I use to flag every record/row where an employee is entitled to an award, or not, in the coming 12 months? tia |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating, filtering, and sorting 5 year anniversaries?
Ricter
what about =datedif(hiredate,today()+365,"y") if it return 5, 10, 15, etc.. are the anniversaries hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Ricter" escreveu: No, I need to identify those anniversaries that will occur within the next 12 months, starting from today. I'm wondering now if I can "bracket" these dates by using your formula for one, and use a variation where I add 12 months for the other... "Marcelo" wrote: Hi, In my understanding you told that if the 5th year will be completed during the current year you should accrual (or something like that) the awards. right? the datedif function has variations that you can use as "Y" - Completed years "M" - Completed months "D" - Days "MD" - diference between the dates but months and years are ignorated "YD" - diference between the dates but days and years are ignorated hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Ricter" escreveu: Gonna have to get a bit more complicated than that methinks. An employee with a hire date of 1/1/2002 will result in 4 years of service, but they will be entitled to their 5th year award within the next 12 months... "Marcelo" wrote: hi =datedif(hiredate,todaydate,"Y") it will return you the difference in completed years hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Ricter" escreveu: If I have an employee database with hire dates, and a company longevity award program that gives an award for each 5th year anniversary, ie. 5, 10, 15, etc. years of service, what formula could I use to flag every record/row where an employee is entitled to an award, or not, in the coming 12 months? tia |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating, filtering, and sorting 5 year anniversaries?
Here's what I've settled on Marcelo, since the user indicates that the
anniversaries are based on the calendar year after all (much easier): A1: =2006-YEAR(HireDate) and B1: =IF(A1/5=INT(A1/5), "Yes", "No") I then do sorts on Column B then Column A to get all the "Yes" together and those sorted by which 5th year anniversary it is. Thanks for your "seed" ideas! "Marcelo" wrote: Ricter what about =datedif(hiredate,today()+365,"y") if it return 5, 10, 15, etc.. are the anniversaries hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Ricter" escreveu: No, I need to identify those anniversaries that will occur within the next 12 months, starting from today. I'm wondering now if I can "bracket" these dates by using your formula for one, and use a variation where I add 12 months for the other... "Marcelo" wrote: Hi, In my understanding you told that if the 5th year will be completed during the current year you should accrual (or something like that) the awards. right? the datedif function has variations that you can use as "Y" - Completed years "M" - Completed months "D" - Days "MD" - diference between the dates but months and years are ignorated "YD" - diference between the dates but days and years are ignorated hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Ricter" escreveu: Gonna have to get a bit more complicated than that methinks. An employee with a hire date of 1/1/2002 will result in 4 years of service, but they will be entitled to their 5th year award within the next 12 months... "Marcelo" wrote: hi =datedif(hiredate,todaydate,"Y") it will return you the difference in completed years hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Ricter" escreveu: If I have an employee database with hire dates, and a company longevity award program that gives an award for each 5th year anniversary, ie. 5, 10, 15, etc. years of service, what formula could I use to flag every record/row where an employee is entitled to an award, or not, in the coming 12 months? tia |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - filtering, sorting? | Excel Worksheet Functions | |||
| Sorting and Filtering by font | Excel Discussion (Misc queries) | |||
| Sorting & Filtering in Shared Workbook mixing up data | Excel Worksheet Functions | |||
| Excel should allow sorting and filtering based on cell color. | Excel Worksheet Functions | |||
| Sorting and filtering | Excel Worksheet Functions | |||
Linear Mode
