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?

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.
--
David Biddulph

"Santa-D" wrote in message
ups.com...
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?

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

--
HTH

Bob Phillips

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

"Santa-D" wrote in message
ups.com...
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?

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


with @tiscali.co.uk


"Santa-D" wrote in message
ups.com...
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?

Sandy Mann wrote:
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?

See my response, it is exactly as you wanted.

--
HTH

Bob Phillips

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

"Santa-D" wrote in message
oups.com...
Sandy Mann wrote:
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?

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)

"Santa-D" wrote in message
oups.com...
Sandy Mann wrote:
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?

Sandy Mann wrote:
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


with @tiscali.co.uk


"Santa-D" wrote in message
ups.com...
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?


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,"m d") & " 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

Bob Phillips wrote:
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 wrote:
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)

"Santa-D" wrote in message
oups.com...
Sandy Mann wrote:
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?

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" wrote in message
ups.com...
Bob Phillips wrote:
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 wrote:
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)

"Santa-D" wrote in message
oups.com...
Sandy Mann wrote:
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 wrote:
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