Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Nigel Welch
 
Posts: n/a
Default 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   Report Post  
Bernard Liengme
 
Posts: n/a
Default

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 wrote in message
...
I have all Add-ins loaded, but still this function is not in the various
function lists



  #3   Report Post  
Bob Phillips
 
Posts: n/a
Default

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" wrote in message
...
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 wrote in message
...
I have all Add-ins loaded, but still this function is not in the various
function lists





  #4   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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 wrote in message
...
I have all Add-ins loaded, but still this function is not in the various
function lists



  #5   Report Post  
Bob Phillips
 
Posts: n/a
Default

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" wrote in message
...
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" wrote in message
...
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 wrote in message
...
I have all Add-ins loaded, but still this function is not in the

various
function lists







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel should have a quick and simple "change case" function like . NinaSvendsen Excel Worksheet Functions 1 January 28th 05 04:15 PM
How do I update Excel 2000 macros to work in Excel 2002? BobPetrich Excel Discussion (Misc queries) 3 January 4th 05 05:06 PM
How do I use Template wizard add in for 2002 Excel in 2003 excel?. TCIrish Excel Worksheet Functions 0 November 23rd 04 05:43 PM
The countif function in Excel 2002. Lounsbud Excel Worksheet Functions 1 November 22nd 04 04:19 PM
Statistical Excel Function Question within Excel 2000... Drew H Excel Worksheet Functions 3 October 31st 04 07:55 PM


All times are GMT +1. The time now is 11:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"