Hello,
Hoping to get some help on a formula as I can't get the result I'm looking for.
I am trying to calculate an adjusted years in service total. I have a employment start date and an end date. This part is pretty straight forward end - start / 365. I need it to be more precise, so I broke it down by months, also simple. Here is the kicker which I haven't found a solution to, any period of more than 6 months after the first year will count as a full year. I thought it would work out using the ceiling.math function with a significance of .5 It works great using that for years of service that equal X.5 and greater, rounds up to the next whole number. What I need it to do however, is if the total years in service <= X.00 - X.49 for it to leave the original number.
In the attached, I'm trying to get column K using the value of column J.
If the value in column J = X.00 to X.49, then K=value in column J
If the value is X.50=X.99, then K=Ceiling.math(j3,0.5)
I don't know how to make the above a reality![]()
Bookmarks