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