DAYS360

What it does

DAYS360(start_date, end_date, [method])

DAYS360 returns the number of days between two dates as counted by a 360-day year of twelve 30-day months — the day-count convention used in bond and accrual arithmetic, not the calendar.

The computation has two steps. First, each endpoint's day-of-month is adjusted according to the selected method; then the count is taken as

360·(y₂ − y₁) + 30·(m₂ − m₁) + (d₂ − d₁)

on the adjusted components. All of the difficulty is in the adjustment, and the adjustment is where the two methods disagree.

The two methods, as documented

Microsoft states them verbatim:

US (NASD) method — method FALSE or omitted. "If the starting date is the last day of a month, it becomes equal to the 30th day of the same month. If the ending date is the last day of a month and the starting date is earlier than the 30th day of a month, the ending date becomes equal to the 1st day of the next month; otherwise the ending date becomes equal to the 30th day of the same month."

European method — method TRUE. "Starting dates and ending dates that occur on the 31st day of a month become equal to the 30th day of the same month."

Read side by side, the difference is sharper than it first appears:

• The European rule is symmetric, unconditional and touches only the 31st. February is never special: 28 February stays 28.
• The US rule is asymmetric and conditional. It treats any month end (including 28 or 29 February) as the 30th when it is the start date; and for the end date it makes the outcome depend on the start date, which is why the US method is not a function of each endpoint independently. The "otherwise" branch — pushing a month-end end-date to the 1st of the next month — is the clause that produces the results people find surprising.

The consequence is that the same pair of dates yields different answers under the two methods, and that is not a bug in either. Any workbook that computes accruals has to know which convention its contracts use.

Arguments

DAYS360(start_date, end_date, [method]) — two required arguments and one optional.

start_date — the beginning of the period. Microsoft's page carries the category's strongest version of the text warning: "Problems can occur if dates are entered as text", with the advice to use DATE or the results of other formulas instead.

end_date — the end of the period. As with plain subtraction, an end before the start gives a negative count; the documentation does not describe an error for that case, unlike DATEDIF.

method — optional logical. FALSE or omitted selects the US (NASD) method; TRUE selects the European method.

The misunderstood argument is method, in a specific and consequential way: it is a logical, and its default is the US convention. A European workbook that omits it silently gets American accrual counts. Because the two methods agree on most ordinary dates and differ only near month ends, the mistake survives casual testing.

Try it

Quirks

Result and edge cases

Returns a Number: a signed integer count of 30/360 days.

• Month ends are the entire subject. For dates that are neither the 30th nor the 31st nor a February month end, the two methods and the plain formula all agree.
• February under the US method is the distinctive case: 28 February (or 29 in a leap year) as a start date is treated as the 30th, so February is stretched to a full 30-day month. The European method leaves it alone.
• The US end-date rule is order-dependent — it consults the start date — so DAYS360 is not antisymmetric under the US method: swapping the arguments need not simply negate the result. That is a genuinely unusual property for an interval function and is worth testing directly.
• The 1900 artefact is largely invisible here, because the computation runs on (year, month, day) components rather than on serial differences — but the components themselves come from the serial, so February 1900 remains a curiosity.

Errors

Microsoft's DAYS360 page describes the methods and the text-entry hazard, but does not publish an enumerated error table of the kind WEEKDAY or NETWORKDAYS.INTL carry. What can be stated honestly:

• #VALUE! for an argument that will not coerce to a number, and propagation of an error argument, are the engine-level behaviours from coercion and lifting.
• Out-of-range serials are #NUM! across this category; the page does not state it for this function specifically, and it is not asserted here as a DAYS360 fact.

How to implement

Notes for implementers

1. Adjust, then subtract — and adjust in the documented order. The US end-date rule reads the unadjusted start date's day-of-month in its condition ("the starting date is earlier than the 30th day of a month"). An implementation that adjusts the start date first and then evaluates the condition against the adjusted value will get a different answer, because the start adjustment can move a 28 to a 30 and flip the test. This is the most likely place for two good-faith implementations to diverge.
2. Do not assume antisymmetry. Under the US method, f(a,b) ≠ −f(b,a) in general. Any optimization that sorts the arguments and negates is wrong.
3. Treat "last day of a month" correctly for February, including 29 February in leap years and the phantom 29 February 1900 (see FUNC.DATE).
4. method is a logical, coerced by the ordinary rules — a numeric 1 or 0, or numeric text, will reach it through the standard to-logical path described in coercion and lifting. The Handbook's chapter notes that to-logical behaviour is per-family; this page does not claim to know DAYS360's.

Rust code

10 days360_fn "DAYS360" (2, 3) [V "start_date", V "end_date", V "method"] |s, a, sv| {
    if let Some(code) = a.iter().find_map(CalcValue::as_error) {
        return fail(code);
    }
    let start = date_arg(s, a, 0)?;
    let end = date_arg(s, a, 1)?;
    let european = a.len() > 2 && num(s, a, 2)? != 0.0;
    out(s, k::days360(start, end, european))
}

fn is_last_day_of_february(year: i64, month: i64, day: i64) -> bool {
    let last = if year == 1900 {
        29
    } else {
        days_in_month(year, month)
    };
    month == 2 && day == last
}

pub(crate) fn days360(start: f64, end: f64, european: bool) -> Result<f64, W> {
    let (sy, sm, mut sd) = ymd_of(start)?;
    let (ey, em, mut ed) = ymd_of(end)?;
    if european {
        if sd == 31 {
            sd = 30;
        }
        if ed == 31 {
            ed = 30;
        }
    } else {
        if sd == 31 || is_last_day_of_february(sy, sm, sd) {
            sd = 30;
        }
        if ed == 31 {
            if sd < 30 {
                let (ny, nm) = if em == 12 { (ey + 1, 1) } else { (ey, em + 1) };
                return Ok((ny - sy) as f64 * 360.0 + (nm - sm) as f64 * 30.0 + (1 - sd) as f64);
            }
            ed = 30;
        }
        if is_last_day_of_february(ey, em, ed) && is_last_day_of_february(sy, sm, sd) {
            ed = 30;
        }
    }
    Ok((ey - sy) as f64 * 360.0 + (em - sm) as f64 * 30.0 + (ed - sd) as f64)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• YEARFRAC — carries the same two conventions as basis 0 (US 30/360) and basis 4 (European 30/360), expressed as a fraction of a year rather than a day count. DAYS360(a, b, FALSE)/360 and YEARFRAC(a, b, 0) are the same quantity on the documented reading — and Microsoft separately warns that YEARFRAC "may return an incorrect result when using the US (NASD) 30/360 basis, and the start_date is the last day in February". Whether DAYS360 carries the same defect, or whether the two disagree on those inputs, is exactly the kind of question a cross-function suite would answer. Nobody has answered it here.
• DAYS and the − operator — actual calendar days. Substituting one for the other changes the financial answer, not just the units.
• DATEDIF with "D" — calendar days again, with a documented #NUM! for reversed arguments that DAYS360 does not appear to share.
• Confusable. DAYS360 and DAYS differ by four characters and by a whole convention; DAYS360(a,b) and DAYS360(a,b,TRUE) differ by a national standard.

Named variants

No named variants are recorded yet.