 Back to jkp-ads.com |
Ron de Bruin
|
 |
|
Back to jkp-ads.com |
Ron de Bruin
|
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
The information from this page is a part of the "Excel Function Bible"
add-in created by
Norman Harker in association with Ron de Bruin.
Description
Calculates the number of days, months, or
years between two dates.
Classification, Source, History
Classification - Date And Time > Date And Time Calculations.
Microsoft sources have said that it was incorporated to ensure
compatibility with Lotus 1-2-3.
Built in.
Mysteriously
omitted from smart tip entry system. Has only had a Help file in Excel 2000.
but the help file is online if you want to see it :
Comment
This is NOT an Analysis ToolPak function. It does exist. It is not a
figment of your imagination. In some situations it is very useful.
Be aware that it does have ideosyncracies and there are significant
bugs. Use with caution and ensure that all mission critical uses are
thoroughly checked against known issues.
Dates in Excel are
specially formatted numbers which Excel stores as numbers but which are
displayed in some form of date representation.
Although the date
serial number system seems obscure, it does fascilitate easy calculations
involving dates.
The function should not be confused with the
DATEDIFF function in VBA.
Syntax:
=DATEDIF(start_date,end_date,unit)
Arguments:
Name Type Description Argument Notes
start_date Required A date that represents the first, or starting, date of
the period for which you want the difference. "Dates may be entered as:
a. text strings within quotation marks (for example, ""2001/1/30""),
b. serial numbers (for example, 36921, or
c. the results of other
formulas or functions (for example, DATEVALUE(""2001/1/30""))
But
note that if the date includes a time portion (a decimal part), DATEDIF
truncates it."
end_date Required A date that represents the second,
or end, date of the period for which you want the difference. "Dates may be
entered as:
a. text strings within quotation marks (for example,
""2001/1/30""),
b. serial numbers (for example, 36921, or
c. the
results of other formulas or functions (for example,
DATEVALUE(""2001/1/30""))
But note that if the date includes a time
portion (a decimal part), DATEDIF truncates it."
unit Required "Unit
is the type of information you want returned.
""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." "Unit requirements may be entered as
a. text strings
in inverted commas or
b. as references to cells containing those strings
(without inverted commas), or
c. as formulas returning those strings
(without inverted commas).
The unit requirements are not case
sensitive."
Related / Similar And Frequently Used With Functions
Related / Similar
DAYS Returns the number of days between two dates.
NETWORKDAYS Returns the number of whole working days between two dates
excluding (if provided) specified holidays.
NETWORKDAYS.INTL Returns the
number of whole working days between two dates allowing weekend day choice
and excluding (if provided) specified holidays.
YEARFRAC Returns the
number of years and fractions of a year between two dates.
Frequently
Used With
DATE Returns the Excel date / time serial number that
represents a particular date.
Examples Using DATE Function
Index
of Examples
Example 1:- Simple Entry Of DATEDIF Function Unit = "d"
Example 2:- Simple Entry Of DATEDIF Function Unit = "m"
Example 3:-
Simple Entry Of DATEDIF Function Unit = "y"
Example 4:- Simple Entry Of
DATEDIF Function Unit = "md"
Example 5:- Simple Entry Of DATEDIF
Function Unit = "ym"
Example 6:- Simple Entry Of DATEDIF Function Unit =
"yd"
a. The formula:
b. Proof of errors using "yd"
c. Method of
avoiding error.
Example 7:- Application Using DATEDIF Function: Eight
Approaches To Calculating Age
a. Age in completed years:
b. Age in
completed months:
c. Age in completed days:
d. Age in years and
completed months:
e. Age in years and days:
f. Age in years, weeks,
and days:
g. Age in years and fractions of a year:
h. Age in years,
months and days:
Errors Using DATEDIF function
Date entry
strings are subject to error because:
a. The month names don’t translate
between different languages.
b. Regional Option setting translate
entries differently. (e.g.) 03-08-2003 is 08-Mar-2003 in English (US)
setting but is 03-Aug-2003 in English (UK) setting;
c. Double digit
years may be interpreted differently. (e.g.) 03-08-03 is in 1903 or 2003
depending upon double digit year interpretation setting.
The solution to
date entry string errors is to avoid them by either:
a. Entering dates
in cells which are validity checked for dates and to refer to those cells.
OR
b. Using the DATE function to enter the start_date and end_date
arguments.
DATEDIF does not allow a negative calculation to be returned.
Thus the first date argument must be an earlier date than the second one.
#VALUE! Is returned if start_date or end_date arguments are not
recognised by DATEDIF as valid dates Be aware that the date validity
algorithm of DATEDIF and Excel generally is different.
#NUM!: is
returned if
the start_date is a later date than the end_date
the
unit argument is not a valid unit argument
Function Bug: Where unit
argument is "yd" there are errors if the start_date and end_date span a Leap
Year day.
This can be a significant issue and we recommend avoidance of
"yd" agrument and favour the formula provided in Example 6 above.