DATA PROCESSING
Conversion of dates from text to date format
Sometimes it can be hard to deal with text and date formats in Excel.
I realized that probably conversion of dates from text to date format issues bother many of you who use Excel daily. So, I hope these insights below will be useful, and that you will go tomorrow to a job and say: “Hey, I know how to make our job easier!”.
Very often you will have a situation in your daily work that you get unprocessed, or to say raw data, which you will need to re-arrange before you could really use them.
When you process data sheets with dates you will face an issue that you will receive data which are unusable for working with date functions such as Networkdays function or Workday function, due to computer date format incompatible with your computer date format settings.
For example, if you use European date format DD.MM.YYYY you may face the situation that your Excel cannot recognize dates written in US date format MM/DD/YYYY especially if data are exported from some other software, put in Excel, and sent to you.
Also, there is a situation when some of your colleagues, less skilled in Excel, put the date in regular format DD.MM.YYYY but including a dot after it. Excel would not recognize this input as a date, but it would assume that your colleague wanted to insert a text string.
In order to use dates from that spreadsheet in Date and Time functions you will need to modify initial file so your cells with dates in format of text become dates in format of a real date recognized by Excel. Maybe I confused you now, but here is what I mean:
These are just few examples. In real life there are few more typing mistakes and date formats in use which could make similar issues to happen.
Now when we understand the challenge, let’s deal with it!
I am sure that some of you are dealing with this issue often by using manual work (read: retyping each date line by line), but let’s discover one simple solution you are going to switch to right away.
If you have the dot after the date you can just use one simple formula to exclude the dot. For example, if your date with wrong format is in the sel C10, then just apply this simple combination of LEFT and LEN function to get rid of the dot in the end:
=LEFT(C10;LEN(C10)-1)
After that you will be able to use the date which got as a formula result in other functions, such as NETWORKDAYS or WORKDAY function.
