What is the exact Excel Days360 algorithm?

Question

I'm porting some calculations from Excel to C# which use the Days360 function (the 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 mostly agree with Excel's native function except if I use 2015-02-28 for both a and b. My code returns 0 and Excel -2.

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

Does anyone know the exact algorithm that Excel uses?

EDIT: There was a glaring bug in the original code I posted which is unrelated to the question. I had already fixed that one but I copied from the wrong file when posting the question.

Answer 1

According to this Wikipedia article the Microsoft Excel Days360 function is equivalent to 30/360 BMA/PSA. So to get exact results as MS Excel we need to implement the BMA/PSA method. I have implemented the 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);
}

Answer 2

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

this is part of the class code for the calculation

    /**
 * 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;
}