Years of Service

E

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".
 
J

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.
 
E

Erinayn

That was exactly what I needed. Thanks!!

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

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 ")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top