Function Problems

Excel Formula to Count Completed 12-Month Periods

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs a formula to calculate the exact number of full 12-month periods (completed years or anniversaries) elapsed from a starting date.

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Tracking anniversaries, employee tenure, or complete years elapsed, ensuring the count updates precisely on the anniversary date rather than comparing calendar years.
Observed behavior: Requires returning the count of full years passed as a whole number without fraction values, accurately reacting to leap years and date offsets.

Before you start

Ensure that your target cells containing the start and end dates are properly formatted as Date values to avoid #VALUE! errors in your calculation.

Solution 1Recommended

Use the DATEDIF Function to Calculate Full Years

The DATEDIF function accurately calculates the number of complete years passed between two dates, perfect for determining completed 12-month periods.

The DATEDIF function is a built-in tool that computes the difference between two dates in years, months, or days. Using the "y" argument ensures that only full calendar years (completed 12-month periods) are counted.

Select the target cell

Click on the cell where you want the completed period count to appear (for example, C2).

Enter the DATEDIF formula

Type the formula =DATEDIF(B$2, B2, "y") where B$2 is your fixed start date and B2 is the date to evaluate. If you need to count the current ongoing period as well, add +1 to the end (e.g., =DATEDIF(B$2, B2, "y")+1).

Adjust cell formatting

Right-click the result cell, select 'Format Cells', choose the 'Number' tab, and set the category to 'General' to display the result as a standard number.

Apply formula to other rows

Click and drag the small square fill handle at the bottom-right corner of cell C2 down the column to apply the calculation to the remaining dates.

Spilled Array Output: If you are using a version of Excel or WPS Spreadsheet that supports dynamic arrays, you can evaluate an entire column simultaneously by entering a range, such as =DATEDIF(B$2, B2:B26, "y")+1.

WPS Spreadsheet Solutions

Easily Calculate Dates and Periods in WPS Spreadsheet

WPS Spreadsheet fully supports the DATEDIF function and dynamic array capabilities, making it incredibly easy to track completed 12-month periods, employee tenures, or project anniversaries.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your start and end dates.
2. Enter the formula: Click an empty cell and type =DATEDIF(Start_Date_Cell, End_Date_Cell, "y") to find the completed years.
3. Fill the column: Use the fill handle to drag down and instantly calculate the 12-month periods for all rows.

Fully compatible with Microsoft Excel formulas, including DATEDIF and YEARFRAC.Lightweight and fast, handling large sets of dates and data with zero lag.Includes hundreds of free built-in templates for tracking milestones and employee anniversaries.Supports dynamic arrays to seamlessly calculate entire columns using a single formula.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the DATEDIF formula returning a #NUM! error?

The #NUM! error occurs if the start date argument is mathematically greater (later in time) than the end date argument. Ensure the first date in the DATEDIF formula is older than the second date.

How can I calculate completed months instead of completed years?

To count completed months, simply change the third argument in your DATEDIF formula from "y" to "m", which looks like this: =DATEDIF(B2, C2, "m").

Why does my result look like a strange date instead of a number?

This happens when the spreadsheet software automatically applies a Date format to your result cell. You can fix this by right-clicking the cell, selecting 'Format Cells', and changing the format category to 'General' or 'Number'.

Can I use the YEARFRAC function to calculate full years?

Yes, you can use =INT(YEARFRAC(start_date, end_date)), which calculates the fraction of years and rounds down to the nearest integer. However, DATEDIF is generally preferred for computing exact completed years, as YEARFRAC logic can sometimes be skewed by leap years.

Excel & Data Problems