# What is the exact algorithm for Excel Days360?

I am migrating some calculations from Excel to C # that use the Days360 function (default / US method). Using the Wikipedia page as a guide, I came up with this code:

``````    public static int Days360(DateTime a, DateTime b)
{
var dayA = a.Day;
var dayB = b.Day;

if (IsLastDayOfFebruary(a) && IsLastDayOfFebruary(b))
dayB = 30;

if (dayA == 31 || IsLastDayOfFebruary(a))
dayA = 30;

if (dayA == 30 && dayB == 31)
dayB = 30;

return ((b.Year - a.Year) * 12 + b.Month - a.Month) * 30 + dayB - dayA;
}

private static bool IsLastDayOfFebruary(DateTime date)
{
if (date.Month != 2)
return false;

int lastDay = DateTime.DaysInMonth(date.Year, 2);
return date.Day == lastDay;
}
```

```

I tested it with a small range of inputs and the results are mostly consistent with the original Excel function, except when I use 2015-02-28 for a and b. My code returns 0 and Excel -2.

My result seems to be more reasonable, but at the moment I would prefer to calculate the same result as Excel. There might be other inputs where they disagree, so I don't want to make a special case for that date only.

Does anyone know the exact algorithm that Excel uses?

EDIT: There was a glaring error in the source code I posted that is not related to the question. I already fixed this, but I copied the wrong file when posting the question.

+3

source to share

According to this Wikipedia article , Microsoft Excel function is `Days360`

equivalent to 30/360 BMA / PSA. Therefore, to obtain accurate results in MS Excel, we need to implement the BMA / PSA method. I have implemented this method.

``````private double Days360(DateTime StartDate, DateTime EndDate)
{
int StartDay = StartDate.Day;
int StartMonth = StartDate.Month;
int StartYear = StartDate.Year;
int EndDay = EndDate.Day;
int EndMonth = EndDate.Month;
int EndYear = EndDate.Year;

if (StartDay == 31 || IsLastDayOfFebruary(StartDate))
{
StartDay = 30;
}

if (StartDay == 30 && EndDay == 31)
{
EndDay = 30;
}

return ((EndYear - StartYear) * 360) + ((EndMonth - StartMonth) * 30) + (EndDay - StartDay);
}

private bool IsLastDayOfFebruary(DateTime date)
{
return date.Month == 2 && date.Day == DateTime.DaysInMonth(date.Year, date.Month);
}
```

```
+2

source

I had the same need, I found the solution in the function on line 51 of this phpexcel dateDiff360 library

this is a part of the class code to calculate

``````    /**
* Identify if a year is a leap year or not
*
* @param    integer    \$year    The year to test
* @return    boolean            TRUE if the year is a leap year, otherwise FALSE
*/
public static function isLeapYear(\$year)
{
return (((\$year % 4) == 0) && ((\$year % 100) != 0) || ((\$year % 400) == 0));
}
/**
* Return the number of days between two dates based on a 360 day calendar
*
* @param    integer    \$startDay        Day of month of the start date
* @param    integer    \$startMonth        Month of the start date
* @param    integer    \$startYear        Year of the start date
* @param    integer    \$endDay            Day of month of the start date
* @param    integer    \$endMonth        Month of the start date
* @param    integer    \$endYear        Year of the start date
* @param    boolean \$methodUS        Whether to use the US method or the European method of calculation
* @return    integer    Number of days between the start date and the end date
*/
private static function dateDiff360(\$startDay, \$startMonth, \$startYear, \$endDay, \$endMonth, \$endYear, \$methodUS)
{
if (\$startDay == 31) {
--\$startDay;
} elseif (\$methodUS && (\$startMonth == 2 && (\$startDay == 29 || (\$startDay == 28 && !self::isLeapYear(\$startYear))))) {
\$startDay = 30;
}
if (\$endDay == 31) {
if (\$methodUS && \$startDay != 30) {
\$endDay = 1;
if (\$endMonth == 12) {
++\$endYear;
\$endMonth = 1;
} else {
++\$endMonth;
}
} else {
\$endDay = 30;
}
}
return \$endDay + \$endMonth * 30 + \$endYear * 360 - \$startDay - \$startMonth * 30 - \$startYear * 360;
}
```

```
+1

source

This algorithm also includes an optional parameter `method`

:

``````int startMonthDays = 0;
int endMonthDays = 0;
double diff = 0;
if(method.Equals("TRUE"))
{

if(dtStartDate.getDay() < 30)
{
startMonthDays = (30 - dtStartDate.getDay());
}
else
{
startMonthDays = 0;
}

if(dtEndDate.getDay() < 30)
{
endMonthDays = dtEndDate.getDay();
}
else
{
endMonthDays = 30;
}

diff =  (dtEndDate.getYear() - dtStartDate.getYear())*360 +
(dtEndDate.getMonth() - dtStartDate.getMonth() - 1)*30 +
startMonthDays + endMonthDays;
}
else
{
if(DateCalendar.daysInMonth(dtStartDate.getYear(), dtStartDate.getMonth()) == dtStartDate.getDay())
{
startMonthDays = 0;
}
else
{
startMonthDays = (30 - dtStartDate.getDay());
}

if(DateCalendar.daysInMonth(dtEndDate.getYear(), dtEndDate.getMonth()) == dtEndDate.getDay())
{
if(dtStartDate.getDay() < DateCalendar.daysInMonth(dtStartDate.getYear(), dtStartDate.getMonth()) - 1)
{
if(DateCalendar.daysInMonth(dtEndDate.getYear(), dtEndDate.getMonth()) > 30)
{
endMonthDays = DateCalendar.daysInMonth(dtEndDate.getYear(), dtEndDate.getMonth());
}
else
{
endMonthDays = dtEndDate.getDay();
}
}
else
{
if(DateCalendar.daysInMonth(dtEndDate.getYear(), dtEndDate.getMonth()) > 30)
{
endMonthDays = DateCalendar.daysInMonth(dtEndDate.getYear(), dtEndDate.getMonth()) - 1;
}
else
{
endMonthDays = dtEndDate.getDay();
}
}
}
else
{
endMonthDays = dtEndDate.getDay();

}

diff =  (dtEndDate.getYear() - dtStartDate.getYear())*360 +
(dtEndDate.getMonth() - dtStartDate.getMonth() - 1)*30 +
startMonthDays + endMonthDays;
}
```

```

from

``````public static int daysInMonth (int year, int month)
{
if (DateTime.IsLeapYear(year) && month == 2)
{
return 29;
}
else
{
return table[month - 1];
}
}
```

```

and

``````private static readonly int[] table = new int[]{ 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
```

```
0

source

Check the following

``````public static int Days360(DateTime a, DateTime b)
{
var dayA = a.Day;
var dayB = b.Day;

if (IsLastDayOfMonth(a) && IsLastDayOfMonth(b)) {
dayB = Math.min(30, dayB);
} else if (dayA == 30 && dayB ==31) {
DayB = 30;
}

if (IsLastDayOfMonth(a))
dayA = 30;

return ((b.Year - a.Year) * 360 + b.Month - a.Month) * 30 + dayB - dayA;
}

private static bool IsLastDayOfMonth(DateTime date)
{
int lastDay = DateTime.DaysInMonth(date.Year, date.Month);
return date.Day == lastDay;
}
```

```
0

source

All Articles