Software
Trying to build a database containing years of service. How do you write the formula for current date and subtract their starting date of hire to obtain years of service.
Thanks
by buchan.jm Accepted solution
by larryd4 Accepted solution
OK so its not perfect but it works..
Say cell A1 has the start date of the employee, which is 5/1/2001, and its in a cell formatted for date.
In cell B1 I enter the following formula;
=(NOW()-A1)/365, which is formulated as a number.
If you run this today the result should be 7.91 which is the years of service.
The NOW() function calls in the current date and time and then subtracts the date in A1. That results in the actual number of days its been since the hire. Then I just divide by 365 days for the average year.
by bizzo Accepted solution
use this:
=TRUNC((NOW()-A1)/365)
by larryd4 Accepted solution
If you use the =Trunc Function you need to specify how much to truncate. Personally I would rather not truncate the data and have output like 7.91 years etc...
A1 = 662.79
B1 = 54.1
=Trunc(A1) would return 662
=Trunc(A1, 0) would return 662
=Trunc(A1, 1) would return 662.7
=Trunc(A2, -1) would return 50
=Trunc(67.891) would return 67
=Trunc(-23.67, 1) would return -23.6
by bizzo Accepted solution
The default value for the second parameter is zero, so it doesn't really need to be there if you're using TRUNC() as a function to return the interger part.
If you wanted to show fractions of years, then I agree, TRUNC() isn't needed. I gave that as an example to show completed years of service.
by larryd4 Accepted solution
We do that here and they always wanted to know how far along they are in their year. I shouldn't have said it as its not needed.
:)
This discussion is closed to new replies.
Sort by Trending