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

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

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.

The same answer but with a different function (that has worked for me):

Public Function DisplayText(ByVal pRange As Range) As String  
  DisplayText = pRange.Text  
End Function  

Just use =DisplayText(A1). If you change the cell format this function will return the displayed text