DATEDIF formula enhancement

[Santa-D]

I've acquired this formula from the Excel 2002 Formula book by John
Walkenbach but I want to extend it's features by rounding it off.

The formula is as follows:

=DATEDIF(G7,H7,"y") & " years, " & DATEDIF(G7,H7,"ym") & " months, " &
DATEDIF(G7,H7,"md")+1 & " days"

G7 = 1/11/2006
H7 = 31/10/2016

The result is 9 years, 11 months, 31 days

What I want it to do is round it to 10 years.

The reason why I can't just go =DATEDIF(G7,H7,"y") & " years" is
because in the same spreadsheet the row above it is:

G7 = 1/03/2006
H7 = 15/11/2006

The result is 0 years, 8 months, 15 days.

I guess the best option would be to create a VBA function which calls
the datediff function?

Any suggestions?

 

[David Biddulph]

What about =DATEDIF(G7,H7,"y")+IF(DATEDIF(G7,H7,"ym")>=6,1) & " years " ?

If you want to be clever you could get it to say "year" when it's 1, and
"years" otherwise, but I'll leave that as an excercise for the interested
reader.

 

[Bob Phillips]

=ROUND(DATEDIF(G7,H7,"M")/12,0)&" years"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Sandy Mann]

If I follow what you are trying to do try:

=DATEDIF(G7,H7+1,"y") & " years, " & DATEDIF(G7,H7+1,"ym") & " months, "
&DATEDIF(G7,H7+1,"md") & " days"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Santa-D]

If I follow what you are trying to do try:

=DATEDIF(G7,H7+1,"y") & " years, " & DATEDIF(G7,H7+1,"ym") & " months, "
&DATEDIF(G7,H7+1,"md") & " days"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

Thanks Sandy, that worked a treat.
The result comes up as "10 years, 0 months, 0 days" what I was hoping
to do was to have the result display as "10 years" and exclude months &
days as they are equal to 0.

I'm not sure if what I want to do can be done in an excel function
without having to enter into VBA.

Can it be done at all?

 

[Bob Phillips]

See my response, it is exactly as you wanted.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

Come to think of it, so does David's, so you had two solutions exactly as
required 11 hours before Sandy posted.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Sandy]

If I follow what you are trying to do try:

=DATEDIF(G7,H7+1,"y") & " years, " & DATEDIF(G7,H7+1,"ym") & " months, "
&DATEDIF(G7,H7+1,"md") & " days"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

To account for zero years, months or days try:

=IF(DATEDIF(G7,H7+1,"y"),DATEDIF(G7,H7+1,"y") & "
year"&IF(DATEDIF(G7,H7+1,"y") >1,"s "," "),"") &
IF(DATEDIF(G7,H7+1,"ym"),DATEDIF(G7,H7+1,"ym") & "
month"&IF(DATEDIF(G7,H7+1,"ym")>1,"s ","
"),"")&IF(DATEDIF(G7,H7+1,"md"),DATEDIF(G7,H7+1,"md") & " day"
&IF(DATEDIF(G7,H7+1,"md")>1,"s",""),"")

Note that the ," " after ,"s " contains a space and there is a space
after the s to provide correct spacing of the words and numbers.


HTH

Sandy

 

[Sandy]

required 11 hours before Sandy posted.

What am I getting a row for being late now? <g>

Correct me if I am wrong Bob but doesn't your formula only deal with
the OP's specific e? I assumed that he would want to be able to enter
various dates and still get a correct answer.

Regards,


Sandy

 

[Bob Phillips]

The response was under Santa-D's post, therefore can easily be construed as
a response to him not you, so there is no reason for you to involve
yourself at all unless you want to.

In response to you, Santa-D then said ...

Thanks Sandy, that worked a treat.
The result comes up as "10 years, 0 months, 0 days" what I was hoping
to do was to have the result display as "10 years" and exclude months &
days as they are equal to 0.

David and I gave him solutions that rounded and only gave years as
requested.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Sandy]

The response was under Santa-D's post, therefore can easily be construed as
a response to him not you, so there is no reason for you to involve
yourself at all unless you want to.

Yes I took it that way Bob, it was just a joke as I hoped my small grin
would show - no offence taken.


Regards,


Sandy