|
DATEDIF formula enhancement
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? |
DATEDIF formula enhancement
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? |
DATEDIF formula enhancement
=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? |
DATEDIF formula enhancement
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? |
DATEDIF formula enhancement
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? |
DATEDIF formula enhancement
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? |
DATEDIF formula enhancement
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 |
DATEDIF formula enhancement
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? |
DATEDIF formula enhancement
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? |
DATEDIF formula enhancement
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 |
| All times are GMT +1. The time now is 05:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com