Function Problems

How to Count Training Dates Within the Last Year by Area in Excel

Chanuka Geekiyanage Oct 22, 2026 869 views

Question details

The user needs to calculate the number of employees who have completed training within the past 12 months, grouped by specific training areas and as an overall total.

How to Count Training Dates Within the Last Year by Area in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking employee training compliance over a rolling 1-year period and segmenting the data by department or area.
Observed behavior: Standard counting methods fail to dynamically update when filtering by area, or return 0 due to data formatting issues such as text-formatted dates or trailing spaces.

Before you start

Ensure your training completion dates are formatted as real Excel dates rather than text, and check that your area names contain no leading or trailing spaces.

Solution 1Recommended

Calculate Area Compliance Using COUNTIFS and EDATE

Use the COUNTIFS function combined with EDATE to count records that match a specific area and occurred within the exact previous 12 months, accounting for leap years.

The COUNTIFS function allows you to apply multiple criteria to your dataset. By combining it with TODAY() and EDATE(), you can create a dynamic formula that always looks exactly 12 months into the past.

Prepare unique area names

List your unique training areas in an empty column (for example, starting in cell E2).

Enter the COUNTIFS formula

In the adjacent cell (F2), enter the formula: =COUNTIFS(B:B,E2,C:C,">="&EDATE(TODAY(),-12)) assuming column B is the training area and column C is the completion date.

Apply to other areas

Press Enter to see the compliance count for that specific area, then drag the fill handle down to apply the formula to the remaining areas.

Calculate overall compliance

To get the overall compliance across all areas, click an empty summary cell and enter the formula: =COUNTIF(C:C,">="&EDATE(TODAY(),-12)).

Calculate Area Compliance Using COUNTIFS and EDATE

Date Accuracy: Using EDATE(TODAY(),-12) is more accurate than TODAY()-365 because it perfectly handles leap years without throwing off the calculation by one day.

Solution 2

Troubleshoot Zero Results by Verifying Data Formats

If your COUNTIFS formula returns 0 unexpectedly, you must check for hidden spaces in your area names and ensure your dates are recognized as numeric values.

Manage Employee Training Data

Use WPS Office to Track Compliance Data Easily

WPS Spreadsheet fully supports advanced functions like COUNTIFS and EDATE. It provides a lightweight, fast, and free platform to handle complex employee training records seamlessly without performance drops.

1. Open your records: Open your training records spreadsheet in WPS Office.
2. Select a target cell: Select the cell where you want the area compliance count to appear.
3. Input the formula: Input the identical =COUNTIFS(B:B,E2,C:C,">="&EDATE(TODAY(),-12)) formula into the formula bar.
4. Fill down: Press Enter, and double-click the small square at the bottom right of the cell to instantly calculate the remaining rows.

100% compatible with Microsoft Excel formulas and formats (.xlsx)Lightweight and fast even with large HR and compliance datasetsBuilt-in data cleaning tools to effortlessly remove spaces and fix text-datesFree to download and use for daily spreadsheet reporting tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my COUNTIFS formula return 0 even though there are matching records?

This usually happens because the dates are stored as text rather than actual Excel dates, or there are trailing spaces in your criteria. Use the ISNUMBER function to verify your dates and the TRIM function to remove hidden spaces from your text.

Can I calculate the percentage of employees who are compliant per area?

Yes. Divide your COUNTIFS area result by the total number of employees in that area. For example: =COUNTIFS(B:B,E2,C:C,">="&EDATE(TODAY(),-12)) / COUNTIF(B:B,E2). Format the resulting cell as a percentage.

Is TODAY()-365 the same as EDATE(TODAY(),-12)?

While similar, EDATE is more precise for rolling year calculations. It automatically adjusts for leap years, whereas subtracting exactly 365 days might shift the calculation by one day during a leap year.

How do I add a second date column to the COUNTIFS formula?

You can add more criteria pairs to the COUNTIFS function. For example, if column D contains an expiration date and you want to ensure the training is still valid today, append ,D:D,">"&TODAY() before the closing parenthesis of your formula.

Excel & Data Problems