 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2008Excel Date Display?
Hello Let's Suppose i gave some date 01-01-2007 in cell A1 and 01-05-2010 in cell b1 if i Subtract Cell B1 from A1 so the Answer will be 1100 Days BUT i want these days as date like 6 days 1 months and 3 Years OR 01/06/03 Is it Possible ? Please Help Try this with the earlier date in A1 =DATEDIF(A1,B1,"y")&" years "&DATEDIF(A1,B1,"ym")&" months "&DATEDIF(A1,B1,"md")&" days" For an explanation of datedif look here http://www.cpearson.com/excel/datedif.aspx and for the occasions when it can go wrong look her http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public .excel.misc&mid=9864766f-755d-4739-8a8c-0a2461bbd23f&sloc=en-us Mike > Hello > Let's Suppose i gave some date 01-01-2007 in cell A1 and 01-05-2010 in cell b1 [quoted text clipped - 9 lines] > Is it Possible ? > Please Help >Hello >Let's Suppose i gave some date 01-01-2007 in cell A1 and 01-05-2010 in cell b1 [quoted text clipped - 9 lines] >Is it Possible ? >Please Help It is possible but difficult because of the variable numbers of days in a month and in a year. Days and Weeks are more precise So you need to be very specific on what you require. For example, the formula Mike posted, which usually works, sometimes doesn't: 31-Jan-2008 29-Feb-2008 --> 0 years 0 months 27 days Should be 29 days 31-Jan-2007 28-Feb-2007 --> 0 years 0 months 25 days Should be 25 days 29-Feb-2008 1-Mar-2008 --> 0 years 0 months 3 days This should be one (1) day If you just need approximations, then the formula probably works most of the time, especially if your starting date is not at the end of the month. --ron Ron, I agree and gave a link to a thread that pointed out those anomolies but the real point for me is why anyone wants to include months in an age calculation in the first place because the varying length makes it a meaningless number. I may be wrong but my guess is that Microsoft never documented datedif in Excel because it's quite the silliest function they ever produced. Mike > >Hello > >Let's Suppose i gave some date 01-01-2007 in cell A1 and 01-05-2010 in cell b1 [quoted text clipped - 29 lines] > time, especially if your starting date is not at the end of the month. > --ron >Ron, > [quoted text clipped - 6 lines] > >Mike Well, it can certainly give inconsistent results. --ron You may be right Mike, but it still doesn't absolve Microsoft. As you can see by the post, people want to express age differences in months. You see it on TV all the time. By producing the function, Microsoft was simply responding to customer demand. If they are going to produce it, they should document it. Regards, Fred. > Ron, > [quoted text clipped - 47 lines] >> time, especially if your starting date is not at the end of the month. >> --ron Hello Ron, For the 3 examples you gave Mike's formula gives me 29, 28 and 1 day respectively which appear to me to be correct, although I think you get odd results if start date is 31st January and end date 1st March.....in which case try this formula =YEAR(B1)-YEAR(A1)-(TEXT(B1,"mmdd")< TEXT(A1,"mmdd"))&" years "&MOD(MONTH(B1)-MONTH(A1)-(DAY(B1)< DAY(A1)),12)&" months "&B1-MIN(DATE(YEAR(B1),MONTH(B1)-(DAY(B1)< DAY(A1))+{1,0},DAY(A1)*{0,1}))&" days" > >Hello > >Let's Suppose i gave some date 01-01-2007 in cell A1 and 01-05-2010 in cell b1 [quoted text clipped - 29 lines] > time, especially if your starting date is not at the end of the month. > --ron >Hello Ron, > >For the 3 examples you gave Mike's formula gives me 29, 28 and 1 day >respectively which appear to me to be correct, although I think you get odd >results if start date is 31st January and end date 1st March.....in which >case try this formula Those are odd results, since I get something quite different. Something strange is going on. Obviously you didn't copy and paste your results, since Mike's formula gives a string. Here is what I get -- copied and pasted: A B C 31-Jan-2008 29-Feb-2008 0 years 0 months 27 days 31-Jan-2007 28-Feb-2007 0 years 0 months 25 days 29-Feb-2008 1-Mar-2008 0 years 0 months 3 days With Mike's formula, also copied and pasted (but dragged down from C1): =DATEDIF(A1,B1,"y")&" years "&DATEDIF(A1,B1,"ym")& " months "&DATEDIF(A1,B1,"md")&" days" It'll be very interesting if this function gives different results in different versions of Excel. I'm using Excel 2007 >I think you get odd results if start date is 31st January and end date 1st March Here's what I get using Mike's formula: 31-Jan-2008 1-Mar-2008 0 years 1 months 1 days That seems like a perfectly reasonable answer. And it is also the same as the answer I get using your formula. --ron Hello, You may not subtract the later date from an earlier date. It will make a negative number. > Hello > Let's Suppose i gave some date 01-01-2007 in cell A1 and 01-05-2010 in cell b1 [quoted text clipped - 9 lines] > Is it Possible ? > Please Help |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.