DateDiff no longer works with interval parameters

looksly

New Member
Joined
Apr 13, 2015
Messages
2
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,300
Office Version
2019, 2016, 2013
Platform
Windows
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

New Member
Joined
Apr 13, 2015
Messages
2
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??
 

Watch MrExcel Video

Forum statistics

Threads
1,095,814
Messages
5,446,654
Members
405,413
Latest member
AlainCar

This Week's Hot Topics

Top