Google-sheets – Custom formatting with data returns what the cell shows but not what the cell contains

formattingformulasgoogle sheets

enter image description here

Basically I have this set up in one of my sheets in which these cells have data validation for "is valid date" so a calendar shows up when you double click them, then it has custom formatting so whichever date you select will make the cell output the day with the ending to that number (1'st',2'nd', etc.). It just appears different but in reality, the full date is still in the cell. My question is, if I set another cell equal to one of the date cells, it doesn't display the whole date, but only what appears in that cell.

Example: If I put =M2 in a random cell, it would show 5th and not 1/5/2019. How can I get it to display 1/5/2019?

Best Answer

  • TO_DATE() formula can convert it back:

=TO_DATE(E3)