“DATE” is written in different formats in different countries, and sometimes even written differently in different parts of a country. You can maintain/change the date format as you wish by customizing the “Region and Language” settings of your windows operating system (these formats are known as system formats). You can make calculation only if the dates in your excel sheet is in the system format only. It’s easy for system to give the number of days in between if we deduct 1-Jan-15 from 1-Jan-16 (365 days), but if one date is written in system format (e.g. 1-Jan-16) and other in some non-system format (e.g. 2016/1/1), then system will give an error. Hence, it is important to have dates in system format. I strongly recommend, if you are using the data for calculation as date, please ensure that the data is in system date format else you may end with wrong result or error.

When you are compiling data received from different territories of your company, and your system date format is “MMDDYYYY”, but data received from one of territory is in “DDMMYYYY” format. You are in a time-bound condition where you need to present data quickly to management and you can’t afford to change thousands of cells by editing individual cells. Now what to do?

Don’t worry! You can convert date in any format to the System Format in seconds with the help of the “Text to Columns” feature of excel. Please follow the instructions given hereunder:

Select the cells of data containing dates you want to convert.
Select “Data” tab and click “Text to Columns”.
Select “Fixed width” from the new window opened and press “Next>” twice.

From new window select “Date” and choose the date format of your data you want to convert from drop-down.
Press “Finish”.

All dates will be converted to your system date format.

Please find below pictorial presentation of commands stated here above for better understanding:

In case you have any query regarding above, you may send the same as comment to this blog.