How to count if dates are in a specific year in Excel?
Suppose you have an employee information table where the employees' names and their dates of birth are stored in two columns. You now need to determine how many employees were born in a particular year—an essential task for activities such as generating HR statistics, planning celebrations, or ensuring compliance with age-related company policies. Accurately identifying the number of records that fall within a given year can be more complicated when date formats vary or when your dataset is large. This article presents two practical methods to quickly count how many dates fall within a specific year using Excel, helping you improve productivity and data accuracy in such scenarios.
Count if dates are in a specific year with formula
Count if dates are in a specific year with an awesome feature
Count if dates are in a specific year with formula
Using an Excel formula enables you to efficiently count the number of date entries that fall within a particular year without manually reviewing your data. This method is suitable for most situations where you prefer not to use additional tools or add-ins. It is especially useful when you often need to reuse or automate this counting process for various datasets or reporting periods.
Syntax
SUMPRODUCT(array1,[array2],[array3],…)
Arguments
Array2, Array3 (Optional): Additional arrays to multiply and add for more complex calculations.
To count the number of dates in a specified year, you can use the following steps:
1. Select a blank cell where you want the count result to appear. Copy and paste the formula below into the selected cell, then press Enter to calculate the result.
=SUMPRODUCT(--(YEAR(B2:B68)=E1))
Note: In this formula, B2:B68 refers to the range containing the date values, and E1 is the cell where you input the year you want to check. Please adjust these references to match the actual layout of your data.
- Be sure to enter the year in cell E1 as a four-digit number (for example, 1988).
- This formula works best when the dates in the selected range are valid Excel date values. Incorrect or text-formatted dates may result in errors or an incorrect count.
- If your dates are stored in different formats or you receive unexpected results, consider converting all date entries to a standard date format before applying this formula.
- If you wish to use the formula for different years or ranges, simply modify the year in E1 or update the range in the formula accordingly.
When using large datasets, keep in mind that arrays like B2:B68 can be adjusted to cover the entire column if needed (for example, B2:B1000). However, try to avoid applying the formula to excessively large or entire column references, as this may slow down your worksheet performance if you have a lot of records.
If you encounter an error, check for empty cells, non-date values, or inconsistencies in your date formatting. Resolving these issues will help ensure the formula provides an accurate count.
Alternative Excel Formula Solution: COUNTIFS
If you prefer a more explicit approach using built-in date criteria, you can use the COUNTIFS function, which can offer better flexibility when you want to specify a complete time range instead of just the year value.
1. Enter the following formula in a blank cell (for example, F1):
=COUNTIFS(B2:B68,">="&DATE(E1,1,1),B2:B68,"<="&DATE(E1,12,31))2. Press Enter to confirm. This formula will count the number of dates in the range B2:B68 that fall within the year entered in cell E1.
If you want to copy this calculation to multiple years, simply adjust cell E1 to reference the desired year or place this formula next to a list of years and drag it down to apply to each one.
The COUNTIFS method provides greater control in situations where you want to count dates within a specific date range (such as fiscal years), or when your criteria need to be more precise than just matching the year number.
Count if dates are in a specific year with an awesome feature
This approach introduces an efficient alternative for users who want a straightforward, interactive way to identify and count date cells matching a particular year. The Select Specific Cells feature in Kutools for Excel enables you to both count and visually highlight all rows with dates from the desired year. This can be particularly advantageous if you need to review or process the selected entries after counting—for example, to copy, delete, or format them.
Before applying Kutools for Excel, please download and install it firstly.
To use this method:
1. Select all cells in your date column. Then, go to Kutools > Select > Select Specific Cells. Refer to the screenshot below:
2. In the Select Specific Cells dialog box that appears, configure the options as follows:
- 2.1 Select Cell under Selection type.
- 2.2 Choose Contains from the Specific type drop-down list.
- 2.3 Enter the specific year you wish to find into the textbox.
- 2.4 Click OK to proceed.
3. After you click OK, another Select Specific Cells dialog will display the total number of cells that contain the specified year. You can then click OK to close the dialog, and all matching cells will be selected in your worksheet for further action.
- This option provides a user-friendly and highly visual way to highlight your results, which is especially helpful when working with extensive lists or when you want a quick overview before proceeding with further steps such as copying or deleting records.
- Ensure date cells are consistently formatted or contain the year value as part of the displayed string. If your dates are formatted as plain numbers or text, consider reformatting them as proper dates prior to applying this tool for best results.
- Kutools also provides the flexibility to perform related operations on the selected cells, making it more efficient for batch processing or further data analysis after counting.
- If you are unable to locate the "Select Specific Cells" feature, please make sure that Kutools for Excel has been properly installed and is enabled within your Excel environment.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Related article
Use countif with multiple criteria in Excel
In Excel, the COUNTIF function helps calculate how many cells contain a specific value, but there are times when you need to count using several criteria at once. This article discusses practical ways to count using multiple criteria for more advanced analysis.
Countif by date/month/year and date range in Excel
Find detailed methods to count entries based not just on year, but by month or within a specific date span, using Excel formulas for a range of time-based counting needs.
Count if cells start with or end with specific text in Excel
Learn easy tricks to count the number of cells beginning or ending with a certain text pattern in your worksheet.
Countif a specific value across multiple worksheets
Need to combine counts from multiple sheets? This article explains how to sum occurrences of a value across several worksheets in Excel.
Convert date to weekday, month, year name or number in Excel
Methods outlined here show you how to extract and display part of a date—such as just the month or weekday name—from a complete date entry.
Convert birthdate to age quickly in Excel
Convert a list of birthdates into age figures effortlessly, with step-by-step formula solutions for fast demographic analysis.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in