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.

Line 1: MsgBox DateDiff("d", DateSerial(2012, 11, 13), DateSerial(2013, 11, 13)) => returned a result of 1, while it should be 365

Line 2: MsgBox DateDiff("d", DateSerial(2012, 11, 13), DateSerial(2012, 11, 14)) => returned a result of 0, while it should be 1
Line 3: MsgBox DateDiff("y", DateSerial(2012, 11, 13), DateSerial(2013, 11, 13)) => run time error, while it should be 1
Line 4: MsgBox DateDiff("m", DateSerial(2012, 11, 13), DateSerial(2012, 12, 13)) => run time error, while it should be 1
Line 5: 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!Not sure why, is this a Mac only problem??

Appreciate any thoughts, Thanks a lot

[Jorge Jaime]

Hi, I have exactly the same problem, it seams like DATEDIFF only returns years difference in VBA for mac!

Sub test()
Dim dif As Integer
date1 = [b16]
date2 = [b17]
dif = DateDiff("yyyy", date1, date2)
End Sub


Unfortunately what I need is months difference, and when i use:

Sub test()
Dim dif As Integer
date1 = [b16]
date2 = [b17]
dif = DateDiff("m", date1, date2)
End Sub

or

Sub test()
Dim dif As Integer
date1 = [b16]
date2 = [b17]
dif = DateDiff("d", date1, date2)
End Sub

I also get years difference!!!

Please help!!!!

Jorge Jaime

[looksly]

I never got an answer to what might have caused the problem. I later upgraded to MS Office 2015 for Mac and the exact same formula is working again.

I have a spreadsheet that capture a datediff using a macro. when I ran the macro in previous version it gave me only difference in years. now the exact same spreadsheet gives me the correct difference in days. so I have this inconsistency in different rows but I didn't bother to fix it.

so my conclusion is that the datediff function is not compatible in a previous version of office.