YearFrac in Google Sheets: Master YEARFRAC for Date Fractions
Learn to use YEARFRAC in Google Sheets to calculate fractional years between dates, explore basis options, and apply practical examples for budgeting, payroll, and scheduling. Clear syntax, troubleshooting tips, and real-world scenarios.
YEARFRAC is a Google Sheets function that returns the fraction of a year between two dates, with an optional basis to control day-count conventions. In Sheets, you typically call YEARFRAC(start_date, end_date, [basis]) to get a decimal value representing the portion of a year elapsed. This quick guide covers syntax, basis options, and practical examples for calendars, budgeting, and payroll.
What YEARFRAC does in Google Sheets
YEARFRAC returns the fraction of a year between two dates. It accepts an optional basis argument that determines day-count conventions. Use it for prorations, project timelines, and payroll calculations. The examples below illustrate core usage and how the basis can shift results across common financial scenarios. YEARFRAC is especially helpful when you need precision beyond whole-year differences and want to model partial periods in budgets or schedules.
=YEARFRAC(DATE(2024,1,1), DATE(2024,12,31), 0)This formula uses basis 0 (30/360) in many regions. If you omit the basis, Google Sheets applies a default convention. In practice, you’ll often compare multiple bases to understand how results change and why your forecasts differ across calendars.
},
Steps
Estimated time: 15-25 minutes
- 1
Prepare date data
Create two date columns in your sheet representing the start and end of each period. Ensure cells are recognized as dates (not text) by applying date formatting or using DATEVALUE if needed.
Tip: Use data validation to prevent non-date entries. - 2
Choose a basis and document it
Decide on a basis value (0–4) that matches your organization's convention and record the choice in a notes column or sheet header to ensure consistency.
Tip: Consistency is key; changing the basis later affects all results. - 3
Enter the YEARFRAC formula
In a third column, enter =YEARFRAC(start_date, end_date, basis). Copy down to apply to all rows.
Tip: If you drag the formula, consider locking references with $ where needed. - 4
Validate and interpret results
Spot-check a few rows against manual day counts or an alternative method; investigate any unexpected deviations around leap years.
Tip: Use a small test table to verify behavior across bases.
Prerequisites
Required
- Required
- A Google Sheet with two date columns (start_date and end_date)Required
- Basic knowledge of date functions (DATE, DATEVALUE)Required
Optional
- Understanding of day-count bases (0-4)Optional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy the cell value or formula. | Ctrl+C |
| PastePaste into destination cell. | Ctrl+V |
| UndoUndo the last action. | Ctrl+Z |
| Fill downFill the formula down a column. | Ctrl+D |
FAQ
What is YEARFRAC used for in Google Sheets?
YEARFRAC calculates the fraction of a year between two dates, useful for prorations, project timelines, and payroll. The basis parameter selects the day-count convention, so be consistent across calculations.
YEARFRAC gives you the year fraction between two dates, with a basis option to control day counting.
What does the basis argument do?
The basis parameter selects the day-count convention used to count days in the period. Different bases yield different fractions, so choose consistently and document your choice.
Basis controls how days are counted; stick with one convention for clarity.
Can I omit the basis?
Yes, you can omit basis; Google Sheets applies a default convention. Including a basis makes results explicit and reproducible.
You can omit it, but specifying a basis makes results predictable.
How do I apply YEARFRAC to many rows?
Use ARRAYFORMULA to apply YEARFRAC across a range, or fill down the formula to extend it.
Use ARRAYFORMULA for bulk calculations.
Why do results look off?
Check date formats and ensure inputs are real dates. Confirm the basis is used consistently across rows.
Dates must be proper date values and the same basis should be used.
The Essentials
- Define a clear basis to ensure consistent fractions
- Convert inputs to proper date values before applying YEARFRAC
- Validate results with sample data and alternative methods
- Use ARRAYFORMULA for bulk YEARFRAC calculations across rows