DATEDIF is not documented in Excel’s Help function. You will not see it appear in the formula bar when you start typing. It is a hidden function, available only to those in the know. And it happens to be exactly what we needed for this task. In Microsoft Excel 2007, with a 97-2003 version spreadsheet file- The cells in column G are formatted as a date (type: 3/14/01). The value of G13 is 6/10/2004 and the value of G14 is 7/12/2004 - showing as 6/10/04 and 7/12/04.
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
![For For](/uploads/1/1/7/9/117990962/615169316.jpg)
Datedif Function Excel For Mac 2016 Download
![Datedif Function Excel For Mac 2016 Datedif Function Excel For Mac 2016](/uploads/1/1/7/9/117990962/266392718.png)
- 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.
- Jun 20, 2017 Okat, Microsoft states that the DATEDIF function 'Calculates the number of days, months, or years between two dates. Warning: Excel provides the DATEDIF function in order to support older workbooks from Lotus 1-2-3. The DATEDIF function may calculate incorrect results under certain scenarios.
- As it turns out, the function exists, as with many other Excel features, purely for compatibility with Lotus Notes 123 – a long since discontinued spreadsheet program. Given there’s no direct alternative for DATEDIF, it’s not clear why this isn’t a fully implemented function, but it exists, and you can use it with most versions on Excel.
Datedif Function Excel Mac
May 24, 2010 11:39 PM