The result for the DATE function is 5/1/2016. Then, we subtract that from the original end date in cell E17, which is 5/6/2016. 5/6/2016 minus 5/1/2016 is 5 days. Need more help? You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice. Instructions in this article apply to Excel 2019, 2016, 2013, 2010, 2007; Excel for Microsoft 365, Excel Online, and Excel for Mac. Build the Excel IF Statement When using the IF, AND, and OR functions, one or all of the conditions must be true for the function to return a TRUE response.
LreAL wrote:
=DATEDIF(”04/06/1988”, NOW(), “Y”) & “ years, “ & DATEDIF(”04/06/1988”, NOW(), “YM”) & “ months, and “ & DATEDIF(”04/06/1988”, NOW(), “MD”) & “ days”
When looking for a syntax error, I often find it useful to break the formula down into its parts. To allow for different date formats (and different dates) with this one, I've also replaced the fixed date literal ('04/06/1988') with a cell reference, and placed the date in that cell.
=
DATEDIF(”04/06/1988”, NOW(), “Y”)
& “ years, “ &
DATEDIF(”04/06/1988”, NOW(), “YM”)
& “ months, and “ &
DATEDIF(”04/06/1988”, NOW(), “MD”)
& “ days”
That wasn't much help in this case, as each part of the formula looks correct.
So I decided to reconstruct the formula. I placed each DATEDIF statement into a separate cell on Row 2, placed the date in C2, and replaced the literal '04/06/1988' with a reference to C. All three parts worked flawlessly with either the literal or the reference to the cell in the same row of column C (which contained the date).
After removing all spaces that were not part of a literal text string (ie. that occurred outside pairs of quotation marks), I started adding the parts of the formula back on to the first part one line at a time, testing after each addition. The formula continued to work on each test, including a test of the final version below.
=DATEDIF(C, NOW(), 'Y')&' years, '&DATEDIF(C, NOW(), 'YM')&' months, and '&DATEDIF(C, NOW(), 'MD')&' days'
I also reinserted the spaces before and after the concatenation operator ( & ), and found their inclusion made no difference to the result.
So I don't know what was the syntax error, but did find that reconstructing the formula removed the error message and returned the correct result.
Regards,
Barry
May 24, 2010 11:39 PM
DATEDIF Worksheetfunction
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.
Datedif Function Excel For Mac 2016 Free
Datedif Function Excel Mac
The Excel DATEDIF function returns the difference between two date values in years, months, or days. The DATEDIF (Date + Dif) function is a 'compatibility' function that comes from Lotus 1-2-3. For reasons unknown, it is only documented in Excel 2000, but you can use it in your formulas in all Excel versions since that time. Nov 12, 2019 The DATEDIF function calculates the period or the difference between two dates in days, months, and years. You can use the DATEDIF function to determine the time frame for an upcoming project, or it can be used, along with a person's birth date, to calculate an individual's age in years, months, and days, for example. How Long an employee has been working for a company? How long our beloved ones lived? What is the age of person in years, months & days? The hidden function.