# Software

## General discussion

Locked

### Calculate Years of Stay

By ·
I have this small project in mind that calculates how long employees stayed so far in a company. I've tried doing it in excel 2000 but can't seem to do it right.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

Collapse -
by In reply to Calculate Years of Stay

Let's keep things simple. Put in the following formula in cell A1:

=today()

This will return the day's date everytime you open the excel file.

Now let's say your database contains a column in which you enter the date of engagement of the employee - let's assume that this will be found in column B. So column B will contain a list of dates of engagement.

In column C enter the following formula:

=\$A\$1-Bn where n=row number

So if the date of engagement of the first employee on the list is in B3, then the formula will read \$A\$1-B3; if in B4 then \$A\$1-B4 - just copy and paste the formula down this column. THIS WILL GIVE THE LENGTH OF THE PERSON'S EMPLOYMENT IN DAYS.

YOu may convert this figure to the (nearest) year by typing in a simple formula such as

=round((C3/365),0)

This will give you the period rounded to the nearest year. Of course you may use any formula you wish - the working part is in column C.

Collapse -

Collapse -
by In reply to Calculate Years of Stay

Point value changed by question poster.

Collapse -
by In reply to Calculate Years of Stay

Assuming that the start date is in A1:
=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days"

See Chip Pearsons Date math section:
http://tinyurl.com/2qwxv
and
http://tinyurl.com/39qen

Collapse -

Collapse -
by In reply to Calculate Years of Stay

This question was closed by the author

## Related Discussions

• 3

Alonshow ·

• 5

#### Learning Management System

shivarajkrv ·

• 0

#### Back to School: How to Leverage People Tracking Tech to Ensure Safety

hildgenmartin2 ·

• 1