I would like a calculation that gives the number of years between 2 dates (Hire Date & Termination Date) but if Termination Date is blank I would like it to use today's date to calculate how many years of service.

I would like the answer to show in years including partition years. I cannot get it to do it and not round up or down. I would like it to show 1.2 years for example.

For SharePoint Online.

 =IF(ISBLANK([Termination Date]),DATEDIF([Hire Date], Today(),"y"),DATEDIF([Hire Date], [Termination Date],"y"))

Would I be able to show years and months? Such as "6 yrs, 11 mths"

1 Answer 1


You can try below formula. In this formula I have considered decimal point as month so in case of 2 years and 11 months it will display 2.11

=CONCATENATE(IF(ISBLANK([Termination Date]),DATEDIF([Hire Date],NOW(),"y"),DATEDIF([Hire Date],[Termination Date],"y")),"  yrs, ",IF(ISBLANK([Termination Date]),DATEDIF([Hire Date],NOW(),"ym"),DATEDIF([Hire Date],[Termination Date],"ym")), " mths")

Below is the screen shot for column creation:

enter image description here

It will give below result in my case.

enter image description here

  • That works thank you!! Is there a way I can show the answer as " 2 yrs, 11 mths"
    – lauri
    Commented Aug 9, 2022 at 23:15
  • I keep trying this but I get an error each time. Exact same parameters - calculated field set to single line of text and copy and pasted formula.
    – lauri
    Commented Aug 10, 2022 at 11:28
  • GOT IT!! In the formula i copped above there are 2 equal signs at the beginning. Thank you!!
    – lauri
    Commented Aug 10, 2022 at 11:32
  • Corrected formula Commented Aug 10, 2022 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.