Years of Service

[Erinayn]

I'm trying to create a "database" for our team so that we can then create a
mail merge for our certificates for anniversaries with the company. I found a
formula that will give me the years of service with some text.

=DATEDIF(G10,$C$1,"y") & " years of service "

G10 = the start date
C1 = today's date

This result gives me "2 years of service" which is great for all but 1 year
of service. Can anyone help me with an if statement for the instance when it
is the first anniversary and I want it to say "1 year of service".

 

[JLatham]

=IF(DATEDIF(G10,C1,"y")>1,DATEDIF(G10,C1,"y") & " years of service
",DATEDIF(G10,C1,"y") & " year of service ")

should do the trick for you.

 

[Erinayn]

That was exactly what I needed. Thanks!!

2 perfect answers in like 15 minutes. I'm going to look like a super star!

 

[JLatham]

Actually, it won't work 'properly' for zero years of service. This would
handle even that situation properly:

=IF(DATEDIF(G10,C1,"y")=1,DATEDIF(G10,C1,"y") & " year of service
",DATEDIF(G10,C1,"y") & " years of service ")