When you get data from other systems (or other countries), you will invariably run into a problem with incompatible date formats. With Nextanalytics for Excel, you can easily adapt to any incoming date format without much effort — and no formulas!

When you load a file into Nextanalytics for Excel, your incoming columns are listed, showing how Excel will interpret them. If you have a date column, and it is not identified as a date, then it probably has an incompatible format. Simply drag the name of the date column from the Incoming box to the Rows box; the contents of the column will be shown in the worksheet in the background.

Switch to the Options tab, and in the Incoming Date Format box, match the characters appropriate for the dates you see in the worksheet. If you click on the Date Help button, a popup will appear with some of the codes possible, as well as a couple of web references you can use for more descriptions (see examples below).

Once you have entered the new format, click the Pivot tab to see how they are interpreted. You will see the column name in blue with a ‘(dates)’ tag, and since the most common report is a trend report, Nextanalytics for Excel will automatically pivot the dates across the columns for you.

To figure out what codes you need to enter, you first have to identify the various components of the dates you have been given, and then place the corresponding codes in the correct order. For example:

1/23/10  would be represented as  M/d/yy

5 JAN 10  would be  d MMM yy

2010-01-23  would be  yyyy-MM-dd

1997-07-16T19:20:30.45+01:00  would be  yyyy-MM-dd’T'HH:mm:ss.ffzzz

 Reference: http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx