We think this user has asked the wrong question. The real challenge isn't finding two formulas, it's deciding what missing data actually means for your workflow. When a date field contains "UN MAR 2022" or "UN UNK 2023," you're not dealing with a formatting problem. You're making a design choice about how your system handles uncertainty. And that choice has consequences.
The proposed solution treats every unknown start date as January 1, 1900, and every unknown end date as December 31, 2025. That works mechanically, but it introduces a silent distortion. A project beginning in 1900 is absurd for any real dataset, it suggests the record is either ancient or corrupted. An end date fixed to 2025 implies that every open-ended timeline converges on the same arbitrary boundary. These aren't neutral defaults. They're assumptions baked into your data model, and if you don't surface them, they will mislead your analysis. The user's example of "UN MAR 2022" returning "01-MAR-22" is actually a workable fallback for partial dates. But converting "UN UNK 2023" to "31-DEC-23" assumes the missing month is December, which is rarely true for a project that ends mid-year.
What this user really needs is a way to preserve the ambiguity while still allowing calculations to run. A better approach is to store the raw text alongside a computed boundary. For start dates, use the first day of the known month and year, or if year is also unknown, flag the record for review. For end dates, use the last day of the known month and year, or if month is unknown, use the last day of December. That keeps your formulas deterministic without pretending you know more than you do. The 1900 and 2025 defaults are too broad, they erase the partial information that is already present.
The practical takeaway is simple: before you write a formula, decide what "unknown" means for your specific use case. If you are tracking historical archives, 1900 might be meaningful. If you are managing active projects, it is a liability. Let the data tell you what it knows, and let your formulas respect the gaps instead of filling them with fiction.