Excel: How Can I Tell If Have Real Dates?

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: How can I tell if I have real dates or text dates? They look alike.

  1. You can't tell by looking if you have real dates.

    Strategy: Go to the Formulas tab and click the Show Formulas icon. (You can also press Ctrl+`, this is the grave accent, often located on the same key as the Tilde, just below Esc on U.S. keyboards.) In Show Formulas mode, real dates will show as serial numbers. Text dates will stay as dates.

  2. In Show Formulas mode, real dates show serial numbers.

    Gotcha: Don't forget to toggle out of Show Formulas mode.

  3. Leaving Excel in Show Formulas mode will confuse everyone.