How To Resolve Date Issues In MS Excel?

How To Resolve Date Issues In MS Excel?

Date format is always a big issue, especially when you are extracting or receiving raw data from different sources(software). It becomes more challenging especially when we get raw data from SAP, ERP, ORACLE, FOXPRO and accounting software etc. We keep on struggling to correct our date format so that we can proceed with report and sometime it takes a lot of time. Remember Date field is very important field and play a vital role in report. In fact your report is incomplete without Date field. We can’t imagine our report unless we correct raw data Date field format as per our system format.

This article will explain you “How to correct Date format” especially when it is not matching with your system Date format.

  1. In your Excel worksheet, select a column of text entries you want to convert to dates(It can correct one column entry at a time).
  2. Go to the Data tab->Data Tools group, and click Text to Columns.
  3. In step 1 of the Convert Text to Columns Wizard, select Delimited and click Next.
  4. In step 2 of the wizard, uncheck all delimiter boxes and click Next.
  5. In the final step, select Date under Column data format, choose the format corresponding to your dates, and click Finish.In this example, we are converting the text dates formatted as “01 02 2015” (month day year), so we select MDY from the drop down box.

Now, Excel recognizes your text strings as dates, automatically converts them to your default date format and displays right-aligned in the cells. You can change the date display format in the usual way via the Format Cells dialog.

Format of a valid date field can be changed by going to format cell. There are various pre-defined formats or an user can customize format by selecting custom format from the list. The keyboard shortcut for format cell is CTRL+1

Important Note. For the Text to Column wizard to work correctly, all of your text strings should be formatted identically. For example, if some of your entries are formatted like day/month/year format while others are month/day/year, you would get incorrect results.


Leave a Reply