A date can look valid after a formula quietly moves it into the next month. Check the date you build against the year, month and day that came in.

Build the date, then compare its components

Enter a modern four-digit year in A2, month in B2 and day in C2 as numbers. In D2 enter =DATE(A2,B2,C2). In E2 use:

=IFERROR(IF(AND(YEAR(D2)=A2,MONTH(D2)=B2,DAY(D2)=C2),D2,"Check date"),"Check date")

Format E as a date. With 2024, 2 and 29, E should show February 29, 2024. With 2025, 2 and 29, D can show March 1, 2025 and E should show Check date.

DATE accepts February 29, 2024. February 29, 2025 rolls to March 1 and is flagged.

Illustrated leap-year check. Keep both the reconstructed value and the source parts for review.

Check a month that goes out of range

For 2025, 13 and 1, D can become January 1, 2026. E should flag it. Keep the original fields so you can ask the source for the intended date. Decide how your import should handle missing components, and use the checked result in the report.

An input month of 13 rolls to January of the next year and fails the component comparison.

Illustrated rollover check. Use the validated date in the report.

Microsoft’s DATE rollover rules