 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / April 2008Numbers 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? On Apr 11, 9:36 am, JustSomeGuy <JustSome...@discussions.microsoft.com> 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 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) On Apr 11, 9:36 am, JustSomeGuy <JustSome...@discussions.microsoft.com> 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 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) > 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? 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. > If the person's hire date is in cell A1 > this formula returns the number of whole years [quoted text clipped - 20 lines] > > anniversary) so the spreadsheet will remain accurate, year after year, > > without a lot of manual updating? 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) > Ron, one other question - if you just wanted to automatically increment a > number when the year turns over, as in the case: [quoted text clipped - 32 lines] >> > anniversary) so the spreadsheet will remain accurate, year after year, >> > without a lot of manual updating? 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. > Ron, one other question - if you just wanted to automatically increment a > number when the year turns over, as in the case: [quoted text clipped - 30 lines] > > > anniversary) so the spreadsheet will remain accurate, year after year, > > > without a lot of manual updating? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.