Hi Paul,
What http://www.cpearson.com/excel/datedif.aspx says is that : The DATEDIF function computes the difference between two dates in a variety of different intervals, such as the number of years, months, or days between the dates. This function is available in all versions of Excel since at least version 5/95, but is documented in the help file only for Excel 2000. For some reason, Microsoft has decided not to document this function in any other versions. DATEDIF is treated as the drunk cousin of the Formula family. Excel knows it lives a happy and useful life, but will not speak of it in polite conversation. Do not confuse the DATEDIF worksheet function with the DateDiff VBA function. Thanks and Regards, Upendra Singh +91-9910227325 From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Paul Schreiner Sent: Friday, October 09, 2009 7:38 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: Subtract dates Interesting. In Excel2007, there is no mention of it, and it is not available in the function wizard. Looking at the documentation link provided, it says it applies to: Microsoft Office SharePoint Server 2007, Windows SharePoint Services 3.0 and is provided for compatibility to Lotus 1-2-3... One thing I noticed though. for the dates, the start date must be before the end date. which make sense if you're READING the dates, but if you're dragging the formula down a couple of thousand lines, you may not always have the two dates in the proper order. I think I'll continue to use things like: abs(date1-date2) so, I learned something new today, can I go home now? thanks! Paul _____ From: Aindril De <aind...@gmail.com> To: excel-macros@googlegroups.com Sent: Friday, October 9, 2009 9:38:01 AM Subject: $$Excel-Macros$$ Re: Subtract dates Hi All, There is a worksheet equivalent of the VBA only datediff function.. It is DateDif. This function calculates the number of days, months, or years between two dates. This function is provided for compatibility with Lotus 1-2-3. Syntax DATEDIF(start_date,end_date,unit) Start_date A date that represents the first, or starting, date of the period. Dates may be entered as text strings within quotation marks (for example, "2001/1/30"), as serial numbers (for example, 36921, which represents January 30, 2001, if you're using the 1900 date system), or as the results of other formulas or functions (for example, DATEVALUE("2001/1/30")). End_date A date that represents the last, or ending, date of the period. Unit The type of information that you want returned: Unit Returns "Y" The number of complete years in the period. "M" The number of complete months in the period. "D" The number of days in the period. "MD" The difference between the days in start_date and end_date. The months and years of the dates are ignored. "YM" The difference between the months in start_date and end_date. The days and years of the dates are ignored "YD" The difference between the days of start_date and end_date. The years of the dates are ignored. Remarks * Dates are stored as sequential serial numbers so they can be used in calculations. By default, December 31, 1899 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. * The DATEDIF function is useful in formulas where you need to calculate an age. Examples Start_date End_date Formula Description (Result) 1/1/2001 1/1/2003 =DATEDIF(Start_date,End_date,"Y") Two complete years in the period (2) 6/1/2001 8/15/2002 =DATEDIF(Start_date,End_date,"D") 440 days between June 1, 2001, and August 15, 2002 (440) 6/1/2001 8/15/2002 =DATEDIF(Start_date,End_date,"YD") 75 days between June 1 and August 15, ignoring the years of the dates (75) 6/1/2001 8/15/2002 =DATEDIF(Start_date,End_date,"MD") The difference between 1 and 15 — the day of start_date and the day of end_date — ignoring the months and the years of the dates (14) Was this information helpful? http://office.microsoft.com/en-us/help/HA011609811033.aspx Regards Andy On Fri, Oct 9, 2009 at 7:52 PM, Upendra Singh <upendrasinghsen...@gmail.com> wrote: Hi Paul, Nopes. I will not format that cell/column as dd-mm-yy. It should be formatted as number. I never used Datediff and so was unaware that it is VBA only function but now I know. Thanks, Upendra Singh +91-9910227325 From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Paul Schreiner Sent: Friday, October 09, 2009 5:23 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: Subtract dates Hmm... I'm not sure I completely agree. what you say at first is true, it is similar to subtracting numbers, because: Excel doesn't really have "dates". It has numbers that are the total days and portions of days since 1/1/1900.. You may choose to DISPLAY this number any way you like.. We choose to DISPLAY it in a form that REPRESENTS a date. But it's still just a number. so, today, 10/9/2009 at 7:43:30 AM, Excel stores as 40095.321875 40,095 days since midnight of 1/1/1900 and .321875 of another day. Now, if I were to subtract Monday's date: 10/5/2009 at 8:00:00 AM, it gives me 3.9885416666657 days. which isn't a problem. But if I put it in "YY-MM-DD" format, it becomes: 00-01-03 which is pretty much meaningless! (DateDiff is a VBA function, not available in Excel) So, yes, you can subtract dates. and yes, you can display it in "YY-MM-DD" format. But are you sure you WANT to? Paul _____ From: Dilip Pandey <dilipan...@gmail.com> To: excel-macros@googlegroups.com Sent: Friday, October 9, 2009 12:59:36 AM Subject: $$Excel-Macros$$ Re: Subtract dates Hi Huzaifa, Yes, it is possible. It is similar as you subtract number in Excel, after that you have to change the cell format to "YY-MM-DD" format. Alternatively, you can also use DATEDIF function, to get the difference as per your choice i.e. months, completed months, years etc. -- DILIP KUMAR PANDEY MBA-HR,B COM(Hons.),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 110062 On 10/2/09, Huzaifa <pastaw...@gmail.com> wrote: Is it possible to subtract dates in Microsoft Excel and get the result in yy-mm-dd format? Please help me. Thanks --~--~---------~--~----~------------~-------~--~----~ ---------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~----------~----~----~----~------~----~------~--~---
