# Looking to add some text in with a formula

1. ## Looking to add some text in with a formula

Hello all,
I have two formulas I'm using to determine years of service & years in grade.

YOS - =IF(H4<>"",DATEDIF(H4,TODAY(),"m")/12,"")

YIG - =IF(H6<>"",DATEDIF(H6,TODAY(),"m")/12,"N/A")

Both H4 & H6 are hire date & promotion date. I'd like to add "Yrs" to the end of my formula, so that it looks like this: "23.18 Yrs" instead of just "23.18".

Any ideas? I tried....I'm quickly running out of hair!

Thanx....LT

2. You mean, like this?

=IF(H4<>"",DATEDIF(H4,TODAY(),"m")/12&" Yrs","")

and

=IF(H6<>"",DATEDIF(H6,TODAY(),"m")/12&" Yrs","N/A")

3. Thanx for the reply,
The one for YOS worked, but it's giving me 13 decimals, I need 2.

The one for YIG comes up "FALSE".

LT

4. tRY:

=IF(H4<>"",ROUND(DATEDIF(H4,TODAY(),"m")/12,2)&" Yrs","")

AND

=IF(H6<>"",ROUND(DATEDIF(H6,TODAY(),"m")/12,2)&" Yrs","N/A")

5. These two worked just fine, thank you!

LT

6. Hello LT,

DATEDIF with "m" only counts the whole months so you are effectively counting in 12ths of a year so it would seem odd, to me, to show the result to 2 decimal places.

For example if an employee has 1 day short of 10 years service then your formula will give "9.92 Yrs". The next day it jumps to "10 Yrs"....and it remains on that figure for a month.

You could get a more accurate figure, perhaps, by using YEARFRAC function from Analysis ToolPak, i.e.

=IF(H4<>"",ROUND(YEARFRAC(TODAY(),H4,1),2)&" Yrs","")

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1