+ Reply to Thread
Results 1 to 5 of 5

Where is DateDiff function in Excel 2002 ?

  1. #1
    Nigel Welch
    Guest

    Where is DateDiff function in Excel 2002 ?

    I have all Add-ins loaded, but still this function is not in the various
    function lists

  2. #2
    Bernard Liengme
    Guest

    Re: Where is DateDiff function in Excel 2002 ?

    It is 'hidden' for reasons know only to Mr Gates & Associates. It was
    mentioned in the Help file of one version (XL 2000 I believe) but it is
    available in all versions. Look at Chip's site www.cpeason.com for details
    on using it.
    best wishes
    --
    Bernard V Liengme
    www.stfx.ca/people/bliengme
    remove caps from email

    "Nigel Welch" <Nigel [email protected]> wrote in message
    news:[email protected]...
    >I have all Add-ins loaded, but still this function is not in the various
    > function lists




  3. #3
    Peo Sjoblom
    Guest

    Re: Where is DateDiff function in Excel 2002 ?

    It was only documented in excel 2000.. It still there in other versions
    It has a few flaws, that was probably the reason. Btw, Datediff is the VBA
    function, Datedif is the built in function


    --

    Regards,

    Peo Sjoblom


    "Nigel Welch" <Nigel [email protected]> wrote in message
    news:[email protected]...
    > I have all Add-ins loaded, but still this function is not in the various
    > function lists




  4. #4
    Bob Phillips
    Guest

    Re: Where is DateDiff function in Excel 2002 ?

    This is what help says for DateDiff

    DateDiff Function
    Returns a Variant (Long) specifying the number of time intervals between two
    specified dates.

    Syntax

    DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

    The DateDiff function syntax has these named arguments:

    Part Description
    interval Required. String expression that is the interval of time you
    use to calculate the difference between date1 and date2.
    date1, date2 Required; Variant (Date). Two dates you want to use in
    the calculation.
    firstdayofweek Optional. A constant that specifies the first day of
    the week. If not specified, Sunday is assumed.
    firstweekofyear Optional. A constant that specifies the first week of
    the year. If not specified, the first week is assumed to be the week in
    which January 1 occurs.



    Settings

    The interval argument has these settings:

    Setting Description
    yyyy Year
    q Quarter
    m Month
    y Day of year
    d Day
    w Weekday
    ww Week
    h Hour
    n Minute
    s Second



    The firstdayofweek argument has these settings:

    Constant Value Description
    vbUseSystem 0 Use the NLS API setting.
    vbSunday 1 Sunday (default)
    vbMonday 2 Monday
    vbTuesday 3 Tuesday
    vbWednesday 4 Wednesday
    vbThursday 5 Thursday
    vbFriday 6 Friday
    vbSaturday 7 Saturday



    Constant Value Description
    vbUseSystem 0 Use the NLS API setting.
    vbFirstJan1 1 Start with week in which January 1 occurs (default).
    vbFirstFourDays 2 Start with the first week that has at least four
    days in the new year.
    vbFirstFullWeek 3 Start with first full week of the year.



    Remarks

    You can use the DateDiff function to determine how many specified time
    intervals exist between two dates. For example, you might use DateDiff to
    calculate the number of days between two dates, or the number of weeks
    between today and the end of the year.

    To calculate the number of days between date1 and date2, you can use either
    Day of year ("y") or Day ("d"). When interval is Weekday ("w"), DateDiff
    returns the number of weeks between the two dates. If date1 falls on a
    Monday, DateDiff counts the number of Mondays until date2. It counts date2
    but not date1. If interval is Week ("ww"), however, the DateDiff function
    returns the number of calendar weeks between the two dates. It counts the
    number of Sundays between date1 and date2. DateDiff counts date2 if it falls
    on a Sunday; but it doesn't count date1, even if it does fall on a Sunday.

    If date1 refers to a later point in time than date2, the DateDiff function
    returns a negative number.

    The firstdayofweek argument affects calculations that use the "w" and "ww"
    interval symbols.

    If date1 or date2 is a date literal, the specified year becomes a permanent
    part of that date. However, if date1 or date2 is enclosed in double
    quotation marks (" "), and you omit the year, the current year is inserted
    in your code each time the date1 or date2 expression is evaluated. This
    makes it possible to write code that can be used in different years.

    When comparing December 31 to January 1 of the immediately succeeding year,
    DateDiff for Year ("yyyy") returns 1 even though only a day has elapsed.

    Note For date1 and date2, if the Calendar property setting is Gregorian,
    the supplied date must be Gregorian. If the calendar is Hijri, the supplied
    date must be Hijri.


    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Bernard Liengme" <[email protected]> wrote in message
    news:[email protected]...
    > It is 'hidden' for reasons know only to Mr Gates & Associates. It was
    > mentioned in the Help file of one version (XL 2000 I believe) but it is
    > available in all versions. Look at Chip's site www.cpeason.com for details
    > on using it.
    > best wishes
    > --
    > Bernard V Liengme
    > www.stfx.ca/people/bliengme
    > remove caps from email
    >
    > "Nigel Welch" <Nigel [email protected]> wrote in message
    > news:[email protected]...
    > >I have all Add-ins loaded, but still this function is not in the various
    > > function lists

    >
    >




  5. #5
    Bob Phillips
    Guest

    Re: Where is DateDiff function in Excel 2002 ?

    Noting what Peo said, this is the Worksheet DateDif help entry
    DATEDIF
    See also

    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 is 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")). For more information about date serial numbers, see
    NOW.

    End_date is a date that represents the last, or ending, date of the
    period.

    Unit is the type of information 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

    a.. Microsoft Excel stores dates as sequential serial numbers so that it
    can perform calculations on them. Excel stores January 1, 1900, as serial
    number 1 if your workbook uses the 1900 date system. If your workbook uses
    the 1904 date system, Excel stores January 1, 1904, as serial number 0
    (January 2, 1904, is serial number 1). For example, in the 1900 date system,
    Excel stores January 1, 1998, as serial number 35796 because it is 35,795
    days after January 1, 1900. Learn more about how Microsoft Excel stores
    dates and times.


    b.. Excel for Windows and Excel for the Macintosh use different date
    systems as their default. For more information, see NOW.
    Examples

    DATEDIF("2001/1/1","2003/1/1","Y") equals 2, or two complete years in the
    period.

    DATEDIF("2001/6/1","2002/8/15","D") equals 440, or 440 days between June 1,
    2001, and August 15, 2002.

    DATEDIF("2001/6/1","2002/8/15","YD") equals 75, or 75 days between June 1
    and August 15, ignoring the years of the dates.

    DATEDIF("2001/6/1","2002/8/15","MD") equals 14, or 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.




    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Bob Phillips" <[email protected]> wrote in message
    news:[email protected]...
    > This is what help says for DateDiff
    >
    > DateDiff Function
    > Returns a Variant (Long) specifying the number of time intervals between

    two
    > specified dates.
    >
    > Syntax
    >
    > DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
    >
    > The DateDiff function syntax has these named arguments:
    >
    > Part Description
    > interval Required. String expression that is the interval of time

    you
    > use to calculate the difference between date1 and date2.
    > date1, date2 Required; Variant (Date). Two dates you want to use in
    > the calculation.
    > firstdayofweek Optional. A constant that specifies the first day of
    > the week. If not specified, Sunday is assumed.
    > firstweekofyear Optional. A constant that specifies the first week

    of
    > the year. If not specified, the first week is assumed to be the week in
    > which January 1 occurs.
    >
    >
    >
    > Settings
    >
    > The interval argument has these settings:
    >
    > Setting Description
    > yyyy Year
    > q Quarter
    > m Month
    > y Day of year
    > d Day
    > w Weekday
    > ww Week
    > h Hour
    > n Minute
    > s Second
    >
    >
    >
    > The firstdayofweek argument has these settings:
    >
    > Constant Value Description
    > vbUseSystem 0 Use the NLS API setting.
    > vbSunday 1 Sunday (default)
    > vbMonday 2 Monday
    > vbTuesday 3 Tuesday
    > vbWednesday 4 Wednesday
    > vbThursday 5 Thursday
    > vbFriday 6 Friday
    > vbSaturday 7 Saturday
    >
    >
    >
    > Constant Value Description
    > vbUseSystem 0 Use the NLS API setting.
    > vbFirstJan1 1 Start with week in which January 1 occurs (default).
    > vbFirstFourDays 2 Start with the first week that has at least four
    > days in the new year.
    > vbFirstFullWeek 3 Start with first full week of the year.
    >
    >
    >
    > Remarks
    >
    > You can use the DateDiff function to determine how many specified time
    > intervals exist between two dates. For example, you might use DateDiff to
    > calculate the number of days between two dates, or the number of weeks
    > between today and the end of the year.
    >
    > To calculate the number of days between date1 and date2, you can use

    either
    > Day of year ("y") or Day ("d"). When interval is Weekday ("w"), DateDiff
    > returns the number of weeks between the two dates. If date1 falls on a
    > Monday, DateDiff counts the number of Mondays until date2. It counts date2
    > but not date1. If interval is Week ("ww"), however, the DateDiff function
    > returns the number of calendar weeks between the two dates. It counts the
    > number of Sundays between date1 and date2. DateDiff counts date2 if it

    falls
    > on a Sunday; but it doesn't count date1, even if it does fall on a Sunday.
    >
    > If date1 refers to a later point in time than date2, the DateDiff function
    > returns a negative number.
    >
    > The firstdayofweek argument affects calculations that use the "w" and "ww"
    > interval symbols.
    >
    > If date1 or date2 is a date literal, the specified year becomes a

    permanent
    > part of that date. However, if date1 or date2 is enclosed in double
    > quotation marks (" "), and you omit the year, the current year is inserted
    > in your code each time the date1 or date2 expression is evaluated. This
    > makes it possible to write code that can be used in different years.
    >
    > When comparing December 31 to January 1 of the immediately succeeding

    year,
    > DateDiff for Year ("yyyy") returns 1 even though only a day has elapsed.
    >
    > Note For date1 and date2, if the Calendar property setting is Gregorian,
    > the supplied date must be Gregorian. If the calendar is Hijri, the

    supplied
    > date must be Hijri.
    >
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Bernard Liengme" <[email protected]> wrote in message
    > news:[email protected]...
    > > It is 'hidden' for reasons know only to Mr Gates & Associates. It was
    > > mentioned in the Help file of one version (XL 2000 I believe) but it is
    > > available in all versions. Look at Chip's site www.cpeason.com for

    details
    > > on using it.
    > > best wishes
    > > --
    > > Bernard V Liengme
    > > www.stfx.ca/people/bliengme
    > > remove caps from email
    > >
    > > "Nigel Welch" <Nigel [email protected]> wrote in message
    > > news:[email protected]...
    > > >I have all Add-ins loaded, but still this function is not in the

    various
    > > > function lists

    > >
    > >

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1