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.


This Post Has 6 Comments

  1. Rodney

    I know this website offers quality depending articles and other data, is there any other website which provides these data
    in quality?

  2. Bettie

    I am sure this piece of writing has touched all
    the internet viewers, its really really pleasant paragraph on building up new weblog.

  3. Ivey

    These are actually enormous ideas in regarding blogging.
    You have touched some fastidious things here. Any way keep
    up wrinting.

  4. Pamela

    It’s very trouble-free to find out any topic on net as compared to books, as I found this post at
    this web page.

  5. Finn

    We’re a group of volunteers and starting a new scheme in our community.

    Your website offered us with valuable information to work on.
    You’ve done an impressive job and our entire community will be grateful to you.

  6. Rosemarie

    Excellent post. I used to be checking continuously this weblog and I’m inspired!
    Very helpful info specially the last phase 🙂 I take care of such info a lot.
    I was seeking this particular info for a very lengthy time.
    Thank you and best of luck.

Leave a Reply