Using If/Then with CONCATENATE

Copper Contributor



I am trying to create a column in a list for a SharePoint site where I am calculating the number of years, months, and days someone has been employed.  Currently I am using the below formula.


=CONCATENATE((ROUNDDOWN(((TODAY()-[Hire Date])/365),1))," yrs")

But now I have run into a snag.  I need to only use the above formula if "rehire Date" is blank.  Otherwise use the "rehire Date" to calculate the years, months, days.


Something like this?

=IF([Rehire Date]="0", =CONCATENATE((ROUNDDOWN(((TODAY()-[Hire Date])/365),1))," yrs"),=CONCATENATE((ROUNDDOWN(((TODAY()-[Rehire Date])/365),1))," yrs")


Any help would be appreciated.



2 Replies

Hi @NitinGautam 

you were nearly there ....this should work

=IF(ISBLANK([Rehire Date]), CONCATENATE((ROUNDDOWN(((TODAY()-[Hire Date])/365),1))," yrs"),CONCATENATE((ROUNDDOWN(((TODAY()-[Rehire Date])/365),1))," yrs"))

Best Regards,


@SvenSieverding Thank you that's perfect.  I knew I was close I just couldn't figure it out.