Calculating Employee Tenure
Hello, I am needing to calculate employee tenure in years and months. I currently have a date field "Start Date" for the date of hire. Is there a formula to easily calculate this? Thank you in ...
Forum Discussion
not tested lately but try this. Just change the first line to use your hire date field.
var date MyDate = [Employee Start Date];
var text Years = ToText(Year(Today())-Year($MyDate) -
If( Month(Today())<Month($MyDate)
or
(Month(Today())=Month($MyDate) and Day(Today())<Day($MyDate)),1,0));
var text Months = If(
Month(Today()) > Month($MyDate), ToText(Month(Today()) - Month($MyDate)),
Month(Today()) = Month($MyDate) and Day(Today()) >= Day($MyDate), ToText(Month(Today()) - Month($MyDate)),
Month(Today()) = Month($MyDate) and Day(Today()) < Day($MyDate), "11",
ToText(12-(Month($MyDate) - Month(Today()))));
var text DaysDisplay = If ( Day(Today()) > Day($MyDate), ToText( Day(Today()) - Day($MyDate) ),
Day(Today()) = Day($MyDate), "0",
Day(Today()) < Day($MyDate), ToText( Day($MyDate)-Day(Today()) ));
var text RawResult=
$Years & " Year" & If ($Years <> "1","s","") & "<br>" &
$Months & " Month" & If($Months<>"1","s", "") "<br>" &
$DaysDisplay & " Day" & If($DaysDisplay<>"1","s", "");
$RawResult
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
var date MyDate = [Employee Start Date];
var text Years = ToText(Year(Today())-Year($MyDate) -
If( Month(Today())<Month($MyDate)
or
(Month(Today())=Month($MyDate) and Day(Today())<Day($MyDate)),1,0));
var text Months = If(
Month(Today()) > Month($MyDate), ToText(Month(Today()) - Month($MyDate)),
Month(Today()) = Month($MyDate) and Day(Today()) >= Day($MyDate), ToText(Month(Today()) - Month($MyDate)),
Month(Today()) = Month($MyDate) and Day(Today()) < Day($MyDate), "11",
ToText(12-(Month($MyDate) - Month(Today()))));
var text DaysDisplay = If ( Day(Today()) > Day($MyDate), ToText( Day(Today()) - Day($MyDate) ),
Day(Today()) = Day($MyDate), "0",
Day(Today()) < Day($MyDate), ToText( Day($MyDate)-Day(Today()) ));
var text RawResult=
$Years & " Year" & If ($Years <> "1","s","") & "<br>" &
$Months & " Month" & If($Months<>"1","s", "") "<br>" &
$DaysDisplay & " Day" & If($DaysDisplay<>"1","s", "");
$RawResult
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
Thank you! Is there an easy way to remove the days calculation from the result? And, is there a way to remove years if an employee has been here for less than a year?
 | Addie Brunson, Operations Analyst Spotlight | www.spotlightar.com 913.375.0574 | |
- not tested by try this
var text RawResult=
IF($Years >0, $Years & " Year" & If ($Years <> "1","s","") & "<br>")
&
$Months & " Month" & If($Months<>"1","s", "");
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
