Thanks Aindril,
I have mentioned to use this function for date related calculations, but you
have provided a good explanation on the usage of this function. Thanks.
Best Regards,
--
DILIP KUMAR PANDEY
MBA-HR,B COM(Hons.),BCA
Mobile: +91 9810929744
dilipan...@gmail.com
dilipan...@yahoo.com
New Delhi - 110062
On Fri, Oct 9, 2009 at 7:08 PM, Aindril De <aind...@gmail.com> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
