DateDiff no longer works with interval parameters

[looksly]

Hi all

I recently realised a DateDiff calculation error in a workbook I have been using for years and found an error. I checked old copies of the same workbook and found the calculated data to be correct, so I am pretty sure the same formula used to work for me. But then even when I ran the old files now the DateDiff function returned the same error.

So here is the error, I am using Excel for Mac 2011 version 14.2.3.


MsgBox DateDiff("d", DateSerial(2012, 11, 13), DateSerial(2013, 11, 13)) => returned a result of 1, while it should be 366
MsgBox DateDiff("d", DateSerial(2012, 11, 13), DateSerial(2012, 11, 14)) => returned a result of 0, while it should be 1
'MsgBox DateDiff("y", DateSerial(2012, 11, 13), DateSerial(2013, 11, 13)) => run time error, while it should be 1
'MsgBox DateDiff("m", DateSerial(2012, 11, 13), DateSerial(2012, 12, 13)) => run time error, while it should be 1
MsgBox DateDiff("q", DateSerial(2012, 11, 13), DateSerial(2013, 11, 13)) => returned a result of 1, while it should be 4

Somehow DateDiff is always giving me the difference in number of years, or doesn't give an answer at all!

Is this a thing with Excel for Mac only? I haven't seen similar problem posted before.

Appreciate any thoughts you might have?

Thanks a lot

 

[mole999]

for line 1 in Excel 2010 I get 365
2 is 1
and
5 is 4

sorry I don't know anything about the mac environment

 

[looksly]

Thanks. That's what I am afraid. I originally started the workbook in Windows and ported to Mac. But I'm pretty sure the function worked (for a period anyway) on my Mac before. I started realising this error now.

A bit more background - I use the workbook to calculate the average holding period of a stock and rate of return. So when I looked at the old files (on Mac) containing past holdings I still see the number of days calculated correctly. Somehow when I run it again, it's returning the strange errors like I said above. Not sure why, is this a Mac only problem??