|
Displaying a decimal as years and months
Hi - need some help here...
Does anybody know how to display a number as years and months? I would like to show 1.8 as 1 year, 9 months instead of a decimal. The 1.8 I get by subrtracting my start date from today's date.... =today()-Cell with start date/365 |
Displaying a decimal as years and months
Have you checked out the "DATEVALUE" function? I think you might be able to
get this to work. "Chris" wrote: Hi - need some help here... Does anybody know how to display a number as years and months? I would like to show 1.8 as 1 year, 9 months instead of a decimal. The 1.8 I get by subrtracting my start date from today's date.... =today()-Cell with start date/365 |
Displaying a decimal as years and months
You could use DATEDIF in your formula to derive the answer in years
and months directly - Chip Peasron explains how he http://www.cpearson.com/excel/datedif.aspx Hope this helps. Pete On Mar 12, 6:52*pm, Chris wrote: Hi - need some help here... Does anybody know how to display a number as years and months? I would like to show 1.8 as 1 year, 9 months instead of a decimal. The 1.8 I get by subrtracting my start date from today's date.... =today()-Cell with start date/365 |
Displaying a decimal as years and months
On Wed, 12 Mar 2008 11:52:00 -0700, Chris
wrote: Hi - need some help here... Does anybody know how to display a number as years and months? I would like to show 1.8 as 1 year, 9 months instead of a decimal. The 1.8 I get by subrtracting my start date from today's date.... =today()-Cell with start date/365 Forget the 1.8. Do this: =DATEDIF(Start_Date,TODAY(),"y") & " years, " & DATEDIF(Start_Date,TODAY(),"ym") & " months" See http://www.cpearson.com/excel/datedif.aspx for documentation of this function. It is present in Excel for many years (at least since 1995) but only documented in 2000. --ron |
Displaying a decimal as years and months
You could try something like this:
=TRUNC((TODAY()-B7)/365.25)&" year, "&MOD(((TODAY()-B7)/365.25), 1)*12&" months" I don't know the Datedif function and I can't find a reference to it in help in Excel 2003, but it would make for a prettier formula. Is Datedif part of toolpack? On Mar 12, 2:52 pm, Chris wrote: Hi - need some help here... Does anybody know how to display a number as years and months? I would like to show 1.8 as 1 year, 9 months instead of a decimal. The 1.8 I get by subrtracting my start date from today's date.... =today()-Cell with start date/365 |
Displaying a decimal as years and months
Chris,
I don't know what version of Excel you are using, but I am using Excel 2007 and it does not include the "DATEDIF" function. When I put that function into the help search field, it showed other ways to get what you want using the Month and Year functions. While DATEDIF sounds neat, it is not in my version of Excel. However, the methods it showed worked very nicely. It is in a section named "Calculate the difference between two dates" and it shows you how to calculate the number of days, the number of months, and the number of years between 2 dates. I think that will give you what you want. "Chris" wrote: Hi - need some help here... Does anybody know how to display a number as years and months? I would like to show 1.8 as 1 year, 9 months instead of a decimal. The 1.8 I get by subrtracting my start date from today's date.... =today()-Cell with start date/365 |
Displaying a decimal as years and months
You've said that Excel 2007 help doesn't mention DATEDIF, but have you tried
using the function in a formula, Les? I expect you'll find it works. For reasons unknown, DATEDIF has been omitted from help in Excel versions apart from 2000, though the function is there. In the absence of help, details are at http://www.cpearson.com/excel/datedif.aspx -- David Biddulph "Les" wrote in message ... Chris, I don't know what version of Excel you are using, but I am using Excel 2007 and it does not include the "DATEDIF" function. When I put that function into the help search field, it showed other ways to get what you want using the Month and Year functions. While DATEDIF sounds neat, it is not in my version of Excel. However, the methods it showed worked very nicely. It is in a section named "Calculate the difference between two dates" and it shows you how to calculate the number of days, the number of months, and the number of years between 2 dates. I think that will give you what you want. "Chris" wrote: Hi - need some help here... Does anybody know how to display a number as years and months? I would like to show 1.8 as 1 year, 9 months instead of a decimal. The 1.8 I get by subrtracting my start date from today's date.... =today()-Cell with start date/365 |
Displaying a decimal as years and months
David,
I did try to use DATEDIF in a formula, but I got the #NAME? when Excel did not recognize the function. I have the Enterprise version of MS Office, so it should be in there... but, alas, it is not. If you think of something else for me to try I will, but for now it looks to me like the function is not included in Excel 2007. Thanks, Les "David Biddulph" wrote: You've said that Excel 2007 help doesn't mention DATEDIF, but have you tried using the function in a formula, Les? I expect you'll find it works. For reasons unknown, DATEDIF has been omitted from help in Excel versions apart from 2000, though the function is there. In the absence of help, details are at http://www.cpearson.com/excel/datedif.aspx -- David Biddulph "Les" wrote in message ... Chris, I don't know what version of Excel you are using, but I am using Excel 2007 and it does not include the "DATEDIF" function. When I put that function into the help search field, it showed other ways to get what you want using the Month and Year functions. While DATEDIF sounds neat, it is not in my version of Excel. However, the methods it showed worked very nicely. It is in a section named "Calculate the difference between two dates" and it shows you how to calculate the number of days, the number of months, and the number of years between 2 dates. I think that will give you what you want. "Chris" wrote: Hi - need some help here... Does anybody know how to display a number as years and months? I would like to show 1.8 as 1 year, 9 months instead of a decimal. The 1.8 I get by subrtracting my start date from today's date.... =today()-Cell with start date/365 |
Displaying a decimal as years and months
On Thu, 13 Mar 2008 10:28:00 -0700, Les wrote:
David, I did try to use DATEDIF in a formula, but I got the #NAME? when Excel did not recognize the function. I have the Enterprise version of MS Office, so it should be in there... but, alas, it is not. If you think of something else for me to try I will, but for now it looks to me like the function is not included in Excel 2007. Thanks, Les Les, I just upgraded to Office 2007 (Standard) and DATEDIF is, indeed, present. With dates in A1 & A2, I used this formula: =DATEDIF(A1,A2,"md") and did NOT get a NAME error. Perhaps if you copy and paste here exactly what you are entering, and exactly what is in your precedent cells, someone might be able to figure out the issue. --ron |
Displaying a decimal as years and months
You haven't fallen into the trap of mis-spelling the function name have you?
It's DATEDIF (with one F) in Excel and DATEDIFF (with 2 Fs) in VBA. -- David Biddulph "Les" wrote in message ... David, I did try to use DATEDIF in a formula, but I got the #NAME? when Excel did not recognize the function. I have the Enterprise version of MS Office, so it should be in there... but, alas, it is not. If you think of something else for me to try I will, but for now it looks to me like the function is not included in Excel 2007. Thanks, Les "David Biddulph" wrote: You've said that Excel 2007 help doesn't mention DATEDIF, but have you tried using the function in a formula, Les? I expect you'll find it works. For reasons unknown, DATEDIF has been omitted from help in Excel versions apart from 2000, though the function is there. In the absence of help, details are at http://www.cpearson.com/excel/datedif.aspx -- David Biddulph "Les" wrote in message ... Chris, I don't know what version of Excel you are using, but I am using Excel 2007 and it does not include the "DATEDIF" function. When I put that function into the help search field, it showed other ways to get what you want using the Month and Year functions. While DATEDIF sounds neat, it is not in my version of Excel. However, the methods it showed worked very nicely. It is in a section named "Calculate the difference between two dates" and it shows you how to calculate the number of days, the number of months, and the number of years between 2 dates. I think that will give you what you want. "Chris" wrote: Hi - need some help here... Does anybody know how to display a number as years and months? I would like to show 1.8 as 1 year, 9 months instead of a decimal. The 1.8 I get by subrtracting my start date from today's date.... =today()-Cell with start date/365 |
Displaying a decimal as years and months
Thank you - this was the answer!!!!
"Pete_UK" wrote: You could use DATEDIF in your formula to derive the answer in years and months directly - Chip Peasron explains how he http://www.cpearson.com/excel/datedif.aspx Hope this helps. Pete On Mar 12, 6:52 pm, Chris wrote: Hi - need some help here... Does anybody know how to display a number as years and months? I would like to show 1.8 as 1 year, 9 months instead of a decimal. The 1.8 I get by subrtracting my start date from today's date.... =today()-Cell with start date/365 |
Displaying a decimal as years and months
Hi - just found that Datedif works - it is part of the ToolPak install. Thank
you for your help!!!! " wrote: You could try something like this: =TRUNC((TODAY()-B7)/365.25)&" year, "&MOD(((TODAY()-B7)/365.25), 1)*12&" months" I don't know the Datedif function and I can't find a reference to it in help in Excel 2003, but it would make for a prettier formula. Is Datedif part of toolpack? On Mar 12, 2:52 pm, Chris wrote: Hi - need some help here... Does anybody know how to display a number as years and months? I would like to show 1.8 as 1 year, 9 months instead of a decimal. The 1.8 I get by subrtracting my start date from today's date.... =today()-Cell with start date/365 |
Displaying a decimal as years and months
Thank you - this was the answer!!!!
"Ron Rosenfeld" wrote: On Wed, 12 Mar 2008 11:52:00 -0700, Chris wrote: Hi - need some help here... Does anybody know how to display a number as years and months? I would like to show 1.8 as 1 year, 9 months instead of a decimal. The 1.8 I get by subrtracting my start date from today's date.... =today()-Cell with start date/365 Forget the 1.8. Do this: =DATEDIF(Start_Date,TODAY(),"y") & " years, " & DATEDIF(Start_Date,TODAY(),"ym") & " months" See http://www.cpearson.com/excel/datedif.aspx for documentation of this function. It is present in Excel for many years (at least since 1995) but only documented in 2000. --ron |
Displaying a decimal as years and months
I think you'll find that DATEDIF is *not* part of the Analysis ToolPak, but
is a native Excel function. -- David Biddulph "Chris" wrote in message ... Hi - just found that Datedif works - it is part of the ToolPak install. Thank you for your help!!!! " wrote: You could try something like this: =TRUNC((TODAY()-B7)/365.25)&" year, "&MOD(((TODAY()-B7)/365.25), 1)*12&" months" I don't know the Datedif function and I can't find a reference to it in help in Excel 2003, but it would make for a prettier formula. Is Datedif part of toolpack? On Mar 12, 2:52 pm, Chris wrote: Hi - need some help here... Does anybody know how to display a number as years and months? I would like to show 1.8 as 1 year, 9 months instead of a decimal. The 1.8 I get by subrtracting my start date from today's date.... =today()-Cell with start date/365 |
| All times are GMT +1. The time now is 03:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com