Function Problems

How to Match Store IDs and Return the Latest Training Date in Excel

Huma Ashraf Ch Oct 10, 2026 870 views

Question details

The user needs an Excel formula to look up a unique store ID across different worksheets and extract the most recent date a training session occurred for that store.

How to Match Store IDs and Find the Latest Training Date in Excel

Product: Excel
Device & OS: not provided
Scenario: Consolidating training records by matching store IDs from a master sheet to a raw data sheet to find the latest training date.
Observed behavior: When applying the MAXIFS formula, it returns the value '1/0/00'. This occurs because the dates in the source sheet are stored as text strings instead of recognized Excel date values.

Before you start

Verify that all training dates in your source worksheet are formatted as actual Excel dates, not text strings, to ensure mathematical calculation functions like MAXIFS work properly.

Solution 1Recommended

Convert Text Dates to Valid Excel Dates

Fixing the underlying data type of the date column is necessary to prevent formulas from returning 1/0/00.

Excel cannot determine the maximum value of dates if they are stored as plain text. You must convert these text entries into serialized numbers that Excel recognizes as dates.

Identify text dates

Navigate to the source worksheet containing your training dates. Dates stored as text often align to the left of the cell by default.

Use the DATEVALUE function

In an empty column next to your text dates, type the formula =DATEVALUE(E2) (assuming your text date is in E2) and press Enter.

Copy and paste as values

Drag the formula down to convert all dates. Then, copy the new column, right-click the original date column, and select 'Paste as Values' to overwrite the text strings.

Apply Date formatting

Highlight the newly pasted values, navigate to the Home tab, click the Number Format dropdown, and select 'Short Date'.

Alternative Method: You can also use the 'Text to Columns' feature under the Data tab. Select the text date column, click 'Text to Columns', hit 'Next' twice, choose 'Date' (MDY), and click 'Finish'.

Solution 2

Apply the MAXIFS Formula Across Worksheets

Use the MAXIFS function to evaluate the highest date value that corresponds to a specific store ID.

Powerful Spreadsheet Alternative

Seamlessly Manage Complex Data Lookups with WPS Spreadsheet

WPS Spreadsheet fully supports advanced formulas like MAXIFS and DATEVALUE. You can effortlessly manage cross-worksheet data consolidation, fix formatting issues, and calculate the latest dates without compatibility concerns.

1. Open your workbook in WPS: Launch WPS Office and open your .xlsx or .xls workbook containing the store IDs.
2. Check date formats instantly: Highlight your date column. Use the WPS number format dropdown on the Home tab to ensure your cells are recognized as Dates rather than Text.
3. Insert the MAXIFS function: Click on your target cell, type the =MAXIFS() formula referencing your cross-sheet data, and press Enter to instantly see the latest training date.

100% compatible with standard Microsoft Excel formulas like MAXIFS, XLOOKUP, and VLOOKUPBuilt-in Text-to-Columns wizard to quickly repair broken date formattingLightweight, lightning-fast performance even with large datasetsCompletely free to download and use for your daily office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my MAXIFS formula return 1/0/00 or 0?

This happens when the criteria range does not match, or more commonly, when the cells in your max_range (the dates) are stored as text rather than valid Excel serial numbers. Using the DATEVALUE function on those cells resolves this issue.

How do I also return the name of the trainer associated with that latest date?

Once you have successfully extracted the latest date using MAXIFS, you can nest that formula inside an XLOOKUP or INDEX/MATCH formula to look up the date and store ID simultaneously and return the corresponding trainer's name.

Can I use MAXIFS if I have older versions of Excel?

The MAXIFS function is available in Office 2019, Office 365, and newer versions. If you are using an older version, you will need to use an array formula combining MAX and IF, entered using Ctrl+Shift+Enter (e.g., =MAX(IF(A:A=D2, B:B)) ). Alternatively, you can use a free modern tool like WPS Spreadsheet, which natively supports MAXIFS.

Excel & Data Problems