What does the basis parameter actually do in YEARFRAC?

The basis parameter selects how to count days and months. Basis 0 (default) assumes 30-day months and 360-day years, simplifying calculations. Basis 1 counts actual calendar days and actual year length, which is more precise but rarely changes results within a single year.

Why does my YEARFRAC formula return a negative number?

YEARFRAC returns negative when start_date is later than end_date. Check that your dates are in the correct order. If intentional, wrap the formula in ABS() to convert to a positive value.

How do I use YEARFRAC to annualize campaign spending or conversions?

Divide the campaign's total spend (or conversions) by its YEARFRAC duration. For example, =5000/YEARFRAC(start, end) projects what the yearly rate would be if the campaign ran for 12 months.

Can I use YEARFRAC if one date is incomplete, like only the month and year?

No, both dates must be complete (day, month, year). If you only have month and year, use DATE(year, month, 1) to create a full date for that month's first day.

YEARFRAC function

YEARFRAC returns the fraction of a year between two dates, useful for annualizing rates and calculating year-based time periods.

=YEARFRAC(start_date, end_date, [basis])

Generate a YEARFRAC formula

Describe what you need. The generator will reach for YEARFRAC where YEARFRAC is the right tool, and tell you when it is not.

Arguments

How YEARFRAC reads its arguments

Argument	Required	Description
start_date	Required	The earlier date (required). Can be a date value or date serial number; if later than end_date, returns a negative fraction.
end_date	Required	The later date (required). Can be a date value or date serial number; if earlier than start_date, the fraction becomes negative.
basis	Optional	Optional day-count convention (0–4): 0=30/360 US, 1=Actual/actual, 2=Actual/360, 3=Actual/365, 4=30E/360 European. Defaults to 0.

Returns

A decimal number between -1 and 1 representing the fractional year between start_date and end_date.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Calculate campaign duration as a year fraction

Campaign	Channel	Start Date	End Date	Spend	Clicks	Conversions
Spring Launch	Email	2025-03-15	2025-05-31	5000	2500	125
Summer Sale	Paid Search	2025-06-01	2025-08-31	8000	4200	210
Holiday Push	Social	2025-11-01	2025-12-31	6500	3100	155

=YEARFRAC(C2,D2)

Result: 0.2192

Spring Launch ran from March 15 to May 31, spanning roughly 78 days. YEARFRAC calculates this as 0.2192, or about 21.9% of a standard year. This fractional representation lets you compare campaign duration across years or annualize metrics.

2. Annualize campaign spend based on duration

Campaign	Channel	Start Date	End Date	Spend	Clicks	Conversions
Spring Launch	Email	2025-03-15	2025-05-31	5000	2500	125
Summer Sale	Paid Search	2025-06-01	2025-08-31	8000	4200	210
Holiday Push	Social	2025-11-01	2025-12-31	6500	3100	155

=E2/YEARFRAC(C2,D2)

Result: 22830.50

Spring Launch spent $5,000 over a 0.2192 year period. Dividing $5,000 by 0.2192 yields $22,830.50, the annualized spend rate. This projection shows how much the campaign would cost if it ran for a full 12 months at the same daily rate.

3. Compare day-count conventions for campaign duration

Campaign	Channel	Start Date	End Date	Spend	Clicks	Conversions
Spring Launch	Email	2025-03-15	2025-05-31	5000	2500	125
Summer Sale	Paid Search	2025-06-01	2025-08-31	8000	4200	210
Holiday Push	Social	2025-11-01	2025-12-31	6500	3100	155

=YEARFRAC(C2,D2,0) vs =YEARFRAC(C2,D2,1)

Result: 0.2192 vs 0.2192

Basis 0 (30/360 US) treats months as 30 days and years as 360, while basis 1 (Actual/actual) counts real calendar days. For most campaigns within the same year, both methods yield nearly identical results. Basis matters more for multi-year periods or precise financial calculations.

Common errors

Which YEARFRAC error are you seeing?

Error	Why it happens	How to fix it
#NUM!	The basis argument is invalid—not an integer between 0 and 4. For example, =YEARFRAC(A1, B1, 5) or =YEARFRAC(A1, B1, -1).	Use basis 0, 1, 2, 3, or 4 only. Omit the argument entirely to default to basis 0.
#VALUE!	start_date or end_date is not recognized as a valid date. Common causes: text that isn't formatted as a date, an empty cell, or a non-date value like a number without context.	Wrap inputs in DATE() or DATEVALUE(), or ensure cells contain recognized date values (not plain text like '2025-03-15').
#NUM!	start_date is later than end_date, producing a negative year fraction. Some spreadsheet systems or workflows flag negative results as calculation errors.	Swap the arguments: use =YEARFRAC(D2, C2) instead of =YEARFRAC(C2, D2), or use ABS(YEARFRAC(...)) to force a positive result.

Tips and when to use something else

Use DAYS or DAYS360 if you need the total day count instead of a fractional year; for example, =DAYS(D2,C2) returns 78 for the Spring Launch campaign.
The basis parameter (0–4) controls how days and months are counted. Basis 0 (30/360) is the default and works for most marketing or business duration calculations.
YEARFRAC is ideal for annualizing campaign metrics: divide total spend or conversions by YEARFRAC to project yearly rates.
When start_date > end_date, YEARFRAC returns a negative fraction. You can catch this with IF() to flag data entry errors: =IF(YEARFRAC(C2,D2)<0, "Error: dates reversed", YEARFRAC(C2,D2)).

Related functions

DAYSDAYS returns the number of calendar days between two dates, with positive or negative result depending on date order.DATEDIFReturns the number of time units (days, months, years) between two dates, useful for calculating durations, aging, or time-to-deadline.DATEDATE returns a date serial number from year, month, and day arguments, enabling date construction from separate numeric components.TODAYTODAY returns the current date as a date value, automatically updating each time the spreadsheet opens or recalculates.EDATEReturns a date that is a specified number of months before or after a start date, making it easy to calculate contract expirations and probation deadlines.

Frequently asked questions

What does the basis parameter actually do in YEARFRAC?: The basis parameter selects how to count days and months. Basis 0 (default) assumes 30-day months and 360-day years, simplifying calculations. Basis 1 counts actual calendar days and actual year length, which is more precise but rarely changes results within a single year.
Why does my YEARFRAC formula return a negative number?: YEARFRAC returns negative when start_date is later than end_date. Check that your dates are in the correct order. If intentional, wrap the formula in ABS() to convert to a positive value.
How do I use YEARFRAC to annualize campaign spending or conversions?: Divide the campaign's total spend (or conversions) by its YEARFRAC duration. For example, =5000/YEARFRAC(start, end) projects what the yearly rate would be if the campaign ran for 12 months.
Can I use YEARFRAC if one date is incomplete, like only the month and year?: No, both dates must be complete (day, month, year). If you only have month and year, use DATE(year, month, 1) to create a full date for that month's first day.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17