General discussion

Locked

Calculate Years of Stay

By shaundyc ·
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.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by aamontalto 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 -

by shaundyc In reply to

thanks for your reply, but cant seem to do it again. maybe you can elaborate more or a ready made will do.

Collapse -

by shaundyc In reply to Calculate Years of Stay

Point value changed by question poster.

Collapse -

by DKlippert 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 -

by shaundyc In reply to
Collapse -

by shaundyc In reply to Calculate Years of Stay

This question was closed by the author

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums