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
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. 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. 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.
May 24, 2010 11:39 PM
Datedif Excel 2016 Missing
The english function name DATEDIF() has been translated into 15 languages. For all other languages, the english function name is used. There are some differences between the translations in different versions of Excel.
Microsoft Excel Datedif Function
- DATEDIF('2016-02-14'; '2016-03-10';'ym') and vba DATEDIFF('m','2016-02-14', '2016-03-10') The reason DateDiff and DateDif gives you two different values in this usage seems to be because one of them calculates the full month difference and the other counts the offset of the months themselves without paying attention to the days.
- The DATEDIF function is a mystery function within Excel. When you write it out in a workbook it doesn’t give you any hints like other functions would and if you look it up in the function list you would not find it!
Availability
Datedif Function Excel For Mac 2016 Product
| Excel Versions | Availability | Category |
|---|---|---|
| Excel (Office 365) | Yes | Hidden |
| Excel 2013 | Yes | Hidden |
| Excel 2010 | Yes | Hidden |
| Excel 2007 | Yes | Hidden |
| Excel 2003 | Yes | Hidden |
Reference language
| Language | Designation |
|---|---|
| English | DATEDIF |
Translations
| Language | Designation |
|---|---|
| Basque | DATADIF |
| Catalan | DIFDATA |
| Danish | DATO.FORSKEL |
| Dutch | DATUMVERSCHIL |
| Finnish | PVMERO |
| Galician | DATADIF |
| Hungarian | DÁTUMTÓLIG |
| Italian | DATA.DIFF |
| Norwegian | DATODIFF |
| Polish | DATA.RÓŻNICA |
| Portuguese, Brazil | DATADIF |
| Portuguese, Portugal | DATADIF |
| Russian | РАЗНДАТ |
| Spanish | SIFECHA |
| Turkish | ETARİHLİ |
Links to the Microsoft Online Help for the function DATEDIF()
Note: Microsoft is currently updating the links and contents for the Excel online help. Therefore, some of the following links may not work as expected and lead to an error page. The links will on this site will be updated as soon as possible.
| Language | |||
|---|---|---|---|
| Arabic | Finnish | Kazakh | Russian |
| Basque | French | Konkani | Serbian |
| Bulgarian | Galician | Korean | Slovak |
| Catalan | German | Latvian | Slovenian |
| Chinese - Simplified | Greek | Lithuanian | Spanish |
| Chinese - Traditional | Gujarati | Malaysian | Swedish |
| Croatian | Hebrew | Marathi | Thai |
| Czech | Hindi | Norwegian | Turkish |
| Danish | Hungarian | Polish | Ukrainian |
| Dutch | Indonesian | Portuguese, Brazil | Vietnamese |
| English | Italian | Portuguese, Portugal | - |
| Estonian | Japanese | Romanian | - |