How to calculate employment length but stop once terminated???

New Contributor

Screenshot 2022-06-01 053658.png

This is the formula I’m using the calculate the time a employee has worked for me. However I’m trying to figure out how to stop the counter once they are terminated. Does anyone have a better formula? 

3 Replies


You'll have to add a column in which you enter the date employment ended. It'll be blank if the person is still employed.

Let's say that you use column J for this. You can then replace TODAY() with IF(J6="",TODAY(),J6) in the formula.


Remark: DATEDIF with "MD" as 3rd argument is not reliable. See DATEDIF function for a workaround.

Could I possibly use column C and somehow include if Terminated end calculation?


That would require VBA, you cannot do that with a formula only.