How to round up/down days in to month

[Tyn]

I have a task that requires calculating the working periods of employees up to date.
I use =DATEDIF(C3;TODAY();"y")&" year"&DATEDIF(C3;TODAY();"ym")&" month"&DATEDIF(C3;TODAY();"md")&" day" and the result is something like "15 years 2 months XX days"
What I want to achieve is rounding up the days, returning the result to something like "15 years 3 months" if the XX is bigger than 15, and "15 years 2 months" if otherwise.
How do I do this? Thanks.
I did think about rounding the initial dates first, then use the =DATEIF... later, but thought it might not as accurate.

 

[Phuoc]

Try this:

=DATEDIF(C3;TODAY();"y")&" years"&DATEDIF(C3;TODAY()-15;"ym")+1&" months"

 

[Tyn]

Hello, thanks alot for the formular, looks like it works but there's a problem, when the normal result is '16 years 11 month 25 days', this formular will result in '16 years 0 month'
How do i fix this?
Also, can you explain what's the bold parts mean in this? Why is it '-15' and '+1' and not the other way around?
Sorry for lots of questions.

 

[Tyn]

After thinking for a bit I did this: the result looks ok. I dont know if you have better solution. Tried this with Sep 1st 2005 - today
=if(DATEDIF(G2;TODAY()+15;"ym")=0; DATEDIF(G2;TODAY();"y")+1&" years "; DATEDIF(G2;TODAY();"y")&" years "&DATEDIF(G2;TODAY()-15;"ym")+1&" months")

 

[bebo021999]

After thinking for a bit I did this: the result looks ok. I dont know if you have better solution. Tried this with Sep 1st 2005 - today
=if(DATEDIF(G2;TODAY()+15;"ym")=0; DATEDIF(G2;TODAY();"y")+1&" years "; DATEDIF(G2;TODAY();"y")&" years "&DATEDIF(G2;TODAY()-15;"ym")+1&" months")

Try to test with 2 Aug 2005 - TODAY

 

[bebo021999]

With start date is in G2, result is in H2.
Try this:

=SUBSTITUTE(DATEDIF(G2,end,"y") & " years " & DATEDIF(G2,end,"ym") & " months "," 0 months","")

with "end" is a Name
Stay in cell H2, hit F3 button to create a user define name:
Name: end
Refer to:

=EDATE(Sheet1!G2,DATEDIF(Sheet1!G2,TODAY(),"y")*12+DATEDIF(Sheet1!G2,TODAY(),"ym")+(DATEDIF(Sheet1!G2,TODAY(),"md")>15))

Or, without name (longer formula)
I2:

=SUBSTITUTE(DATEDIF(G2,EDATE(G2,DATEDIF(G2,TODAY(),"y")*12+DATEDIF(G2,TODAY(),"ym")+(DATEDIF(G2,TODAY(),"md")>15)),"y") & " years " &
 DATEDIF(G2,EDATE(G2,DATEDIF(G2,TODAY(),"y")*12+DATEDIF(G2,TODAY(),"ym")+(DATEDIF(G2,TODAY(),"md")>15)),"ym") & " months "," 0 months","")

Book1
	G	H	I
2	01/08/2005	17 years 1 months	17 years 1 months
Sheet1

Cell Formulas
Range	Formula
H2	H2	=SUBSTITUTE(DATEDIF(G2,end,"y") & " years " & DATEDIF(G2,end,"ym") & " months "," 0 months","")
I2	I2	=SUBSTITUTE(DATEDIF(G2,EDATE(G2,DATEDIF(G2,TODAY(),"y")*12+DATEDIF(G2,TODAY(),"ym")+(DATEDIF(G2,TODAY(),"md")>15)),"y") & " years " & DATEDIF(G2,EDATE(G2,DATEDIF(G2,TODAY(),"y")*12+DATEDIF(G2,TODAY(),"ym")+(DATEDIF(G2,TODAY(),"md")>15)),"ym") & " months "," 0 months","")