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
-~----------~----~----~----~------~----~------~--~---
