Confusing Excel MOD Function Results

**boriszone** · 09-29-2016, 01:56 PM

I’m using the mod function as follows:

Mod(Date 1 – Date 2, 365), where date 1 is a fixed date in time (say 9/1/2010) and date 2 is last day of each month in the future (e.g., 8/31/16).

You’d think that the MOD function would give the exact same result for each month end date, right? Not so:

• =MOD(9/1/10-8/31/10,365)=1
• =MOD(9/1/10-8/31/11,365)=1
• =MOD(9/1/10-8/31/12,365)=0
• =MOD(9/1/10-8/31/13,365)=0
• =MOD(9/1/10-8/31/14,365)=0
• =MOD(9/1/10-8/31/15,365)=0
• =MOD(9/1/10-8/31/16,365)=364

Thoughts?

**Jonmo1** · 09-29-2016, 02:02 PM

THose are not being read like dates.
=MOD(9/1/10-8/31/10,365)=1

That's being read like
9 divided by 1 divided by 10 minus 8 divided by 31 divided by 10

Try
=MOD(DATE(2010,9,1)-DATE(2010,8,31),365)=1

**MrShorty** · 09-29-2016, 02:13 PM

I expect it has to do with leap years. A year is not exactly 365 days. So we have leap years (366 days) to account for the difference. Looking at your examples, the mod function output changes with each leap year (2012 and 2016), and, at first glance, the change in the output seems consistent with those years being longer than 365 days.

With that in mind, what exactly are you trying to get from your mod function? Are you familiar with how Excel stores dates and times as serial numbers (http://www.cpearson.com/Excel/datetime.htm#SerialDates )?