Numbers that self-increment when the year rolls over

[JustSomeGuy]

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?

 

[cht13er]

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

 

[Ron Coderre]

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)

 

[Ron Coderre]

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)

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

 

[JustSomeGuy]

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]

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)

 

[JLGWhiz]

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.