Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Numbers that self-increment when the year rolls over
One column in a personnel roster spreadsheet contains the number of years
each person has worked for the company. Is there a way to make this number for each person self-increment (ideally on each person's hire date anniversary) so the spreadsheet will remain accurate, year after year, without a lot of manual updating? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Numbers that self-increment when the year rolls over
On Apr 11, 9:36*am, JustSomeGuy
wrote: One column in a personnel roster spreadsheet contains the number of years each person has worked for the company. Is there a way to make this number for each person self-increment (ideally on each person's hire date anniversary) so the spreadsheet will remain accurate, year after year, without a lot of manual updating? Have a column with the person's start date e.g March 12, 2005, and another column with this formula: "=YEAR(NOW()-(D6-1))-1900" (where D6 is that person's start date) ... if you like, you can format the column (Format-Cells-Custom) as "## "Years Employed""! HTH Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Numbers that self-increment when the year rolls over
If the person's hire date is in cell A1
this formula returns the number of whole years the employee has worked for the company: B1: =DATEDIF(A1,TODAY(),"y")&" years " For more information, see Chip Pearson's coverage of Excel's (mostly) undocumented DATEDIF function: http://www.cpearson.com/excel/datedif.htm Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "JustSomeGuy" wrote in message ... One column in a personnel roster spreadsheet contains the number of years each person has worked for the company. Is there a way to make this number for each person self-increment (ideally on each person's hire date anniversary) so the spreadsheet will remain accurate, year after year, without a lot of manual updating? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Numbers that self-increment when the year rolls over
Haven't explored where that formula mis-calculates,
but when I run the below test, The values are generally understated by 1. StartDate CurrDate YourFormula Using_Datedif 1/1/2002 1/1/2008 5 6 1/1/2003 1/1/2008 4 5 1/1/2004 1/1/2008 4 4 1/1/2005 1/1/2008 2 3 1/1/2006 1/1/2008 1 2 1/1/2007 1/1/2008 0 1 -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "cht13er" wrote in message ... On Apr 11, 9:36 am, JustSomeGuy wrote: One column in a personnel roster spreadsheet contains the number of years each person has worked for the company. Is there a way to make this number for each person self-increment (ideally on each person's hire date anniversary) so the spreadsheet will remain accurate, year after year, without a lot of manual updating? Have a column with the person's start date e.g March 12, 2005, and another column with this formula: "=YEAR(NOW()-(D6-1))-1900" (where D6 is that person's start date) ... if you like, you can format the column (Format-Cells-Custom) as "## "Years Employed""! HTH Chris |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Numbers that self-increment when the year rolls over
Ron, one other question - if you just wanted to automatically increment a
number when the year turns over, as in the case: Joe B. has over 18 years with Company X (year rolls over) Joe B. has over 19 years with Company X maybe that would be simpler - it doesn't require you to know the person's exact ddmmyy of hire, just the year. "Ron Coderre" wrote: If the person's hire date is in cell A1 this formula returns the number of whole years the employee has worked for the company: B1: =DATEDIF(A1,TODAY(),"y")&" years " For more information, see Chip Pearson's coverage of Excel's (mostly) undocumented DATEDIF function: http://www.cpearson.com/excel/datedif.htm Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "JustSomeGuy" wrote in message ... One column in a personnel roster spreadsheet contains the number of years each person has worked for the company. Is there a way to make this number for each person self-increment (ideally on each person's hire date anniversary) so the spreadsheet will remain accurate, year after year, without a lot of manual updating? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Numbers that self-increment when the year rolls over
So...if Joe B. is hired on 31-Dec-2007, all you'd use is the year?
What would the 01-Jan-2008 formula indicate: Joe B. has over 1 year with Company X or, perhaps just as bad: Joe B. has over 0 years with Company X Since it's your formula......it's you who decides what works and what doesn't. If you're building text for a sentence, perhaps: Joe B. has been with Company X since YEAR or Joe B. has been with Company X since MONTH-YEAR. If it was my application, I'd want the hire date. That way I'd have the flexibility to change the displayed value to whatever I wanted. Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "JustSomeGuy" wrote in message ... Ron, one other question - if you just wanted to automatically increment a number when the year turns over, as in the case: Joe B. has over 18 years with Company X (year rolls over) Joe B. has over 19 years with Company X maybe that would be simpler - it doesn't require you to know the person's exact ddmmyy of hire, just the year. "Ron Coderre" wrote: If the person's hire date is in cell A1 this formula returns the number of whole years the employee has worked for the company: B1: =DATEDIF(A1,TODAY(),"y")&" years " For more information, see Chip Pearson's coverage of Excel's (mostly) undocumented DATEDIF function: http://www.cpearson.com/excel/datedif.htm Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "JustSomeGuy" wrote in message ... One column in a personnel roster spreadsheet contains the number of years each person has worked for the company. Is there a way to make this number for each person self-increment (ideally on each person's hire date anniversary) so the spreadsheet will remain accurate, year after year, without a lot of manual updating? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Numbers that self-increment when the year rolls over
It would depend on how legally correct the data has to be. If you want it
for general use you could set up all the employees years of service in one column and make everything in that column increment by 1 on 1 January of each year. However, if you are tracking seniority, vacation accrual, pay reviews, etc. you would probably need to track their logevity by hire date. But what do I know, I'm retired. "JustSomeGuy" wrote: Ron, one other question - if you just wanted to automatically increment a number when the year turns over, as in the case: Joe B. has over 18 years with Company X (year rolls over) Joe B. has over 19 years with Company X maybe that would be simpler - it doesn't require you to know the person's exact ddmmyy of hire, just the year. "Ron Coderre" wrote: If the person's hire date is in cell A1 this formula returns the number of whole years the employee has worked for the company: B1: =DATEDIF(A1,TODAY(),"y")&" years " For more information, see Chip Pearson's coverage of Excel's (mostly) undocumented DATEDIF function: http://www.cpearson.com/excel/datedif.htm Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "JustSomeGuy" wrote in message ... One column in a personnel roster spreadsheet contains the number of years each person has worked for the company. Is there a way to make this number for each person self-increment (ideally on each person's hire date anniversary) so the spreadsheet will remain accurate, year after year, without a lot of manual updating? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I cannot unhide rolls | New Users to Excel | |||
Linear trend function: 5 year increment | Excel Discussion (Misc queries) | |||
53 weeks in 2004 skews x-axis year increment on charts. | Charts and Charting in Excel | |||
add rolls automatically at top | Excel Worksheet Functions | |||
Increment Month/Year in "mmm-yy" format | Excel Programming |