Mastering YEARFRAC in Google Sheets: A Practical Guide

YEARFRAC basics in Google Sheets

YEARFRAC returns the fraction of a year between two dates using a day-count basis. The function signature is =YEARFRAC(start_date, end_date, [basis]), where basis can be 0–4 corresponding to different conventions. This makes it ideal for budgeting, project planning, and any analysis requiring precise year-length calculations. When you pass dates as strings, Google Sheets converts them automatically if they are recognizable. The result is a decimal value representing elapsed years, which you can multiply by annual figures or convert to months as needed.

This example uses the Actual/Actual basis (1) and returns approximately 0.997 years. You can also use string dates, but DATEVALUE or DATE is safer for reliability.

Calculating fractions between two specific dates

In practice, you often compare two dates in cells, such as A2 and B2. The YEARFRAC formula below computes the fraction of a year between these dates with a consistent basis. The result can be used directly in percentages or multiplied by annual budgets.

If A2 and B2 are text dates, wrap them with DATEVALUE to ensure proper conversion:

This approach reduces errors from ambiguous date formats, especially in international datasets.

Choosing the right day-count basis

Google Sheets supports five basis options: 0 through 4. Each basis reflects a different convention for counting days in a year. Basis 1 (Actual/Actual) is common for financial calculations, while Basis 0 (US 30/360) is helpful for simplified calendars. Use the same basis across your dataset to ensure comparability.

Compare results to understand how basis selection affects the fraction.

Applying YEARFRAC to a column with ARRAYFORMULA

To scale YEARFRAC across many rows, ARRAYFORMULA lets you apply the calculation to an entire column without dragging. Ensure your date pairs are aligned (A2:A with B2:B). The formula below computes the year fraction for each row using the Actual/Actual basis (1).

This produces a column of results that updates automatically as dates are added.

Handling dates and text input robustly

Dates can arrive as text in real datasets. Convert them with DATEVALUE or DATE to avoid misinterpretation. The following examples demonstrate safe conversion before applying YEARFRAC.

If either date is missing, you can guard with IFERROR to return a friendly message:

Practical budgeting and forecasting use cases

YEARFRAC supports budgeting by converting date spans into year-based factors. For example, you can compute the fraction of a year between project start and end dates and multiply by annual costs to estimate mid-year burn. Here’s a compact example using explicit dates:

This yields an estimated pro-rated cost for the defined period. You can expand this with ARRAYFORMULA for multiple projects:

Comparing bases and validating results

If you’re unsure which basis to apply, compute the same span with multiple bases and compare. This helps in audit trails and documentation. Example:

Note that 0 and 4 often yield different fractions due to day-count conventions. Validate against your domain standard before finalizing.

Troubleshooting common issues

Date parsing failures are the most common source of errors. Always normalize inputs with DATE, DATEVALUE, or DATEVALUE(DATE()) before YEARFRAC. If dates are reversed (start after end), YEARFRAC still returns a value, but sometimes it’s negative depending on basis. Guard with an absolute check:

This clarifies interpretation and prevents silent logic errors.

Extending YEARFRAC with related functions

Combine YEARFRAC with other date functions for richer analytics. For example, compare two spans or convert the result to months or days:

To convert the year fraction to days approximately, multiply by 365:

You can also subtract fractions to measure duration differences: