Excel: 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: Flash Fill won't fill dates. When I use Ctrl+E, it won't get them right.

Strategy: First, the Text to Columns command would handle dates better than Flash Fill. But, if you need to use Flash Fill, follow these steps:

  1. Choose the column where you want the dates to fill.
  2. You have to apply a date format that has two spaces for month and four for year. Press Ctrl+1 to display the Format Cells dialog. On the Number tab, choose the Custom category. Type a date format of MM/DD/YYYY.
  3. Fill in the first date.
  4. Go to the blank cell below that date.
  5. Ctrl+E to Flash Fill.
  6. You are now free to select the column and apply another date format.