How can I get the displayed value of a cell in MS Excel ( for text that was converted to dates)?

If you have shown us the full range of possible formats, this should work:

=TEXT(A1, IF(CELL("format",A1)="D4", "m/d/yy", "General"))

If you have date formats other than m/d/yy, add tests for them.

A general answer:

Rather than trying to fuss with the results of the CELL("format"...) function, it may be easier to use a user-defined function to return the NumberFormat property of the cell directly.

In that case, the expression "=TEXT(A1,NumberFormat(A1))" would give you the displayed value rather directly.

To enable this, you need the following in a module of the worksheet:

  Public Function NumberFormat(CellRange As Range) As String

  NumberFormat = CellRange.NumberFormat

  End Function