Data analysis especially for business purposes most times require that the analyst makes use of dates in Microsoft Excel to provide answers to certain questions for example, “how much was made on this day?” or “how does the sale of this week compare with the sale last week?” Providing answers to this questions can prove difficult if Excel does not understand that dates are a requirement.
One thing we must note however is that this isn’t an entirely common thing mostly in a situation where more than one person is typing the same data, copying and pasting from another system and importation from a database.
Dates that Contain a Full Stop/Period
One mistake that beginner analysts sometimes make is typing dates into Excel and separate day, month, and year with the Full Stop character.
Excel will definitely not view this as a date, rather it will think it’s a text. But this issue can be solved using the Find and Replace tool. By replacing the full stops with the slash character (/) and then Excel will know that it’s a date.
Choose the columns in which you want to do the find and replace.
Select Home on the Ribbon, then click Find and Select, click Replace or press Ctrl+H on the keyboard.
Once the Find and Replace window opens, type in a full stop (.) in the “Find what” field box and slash (/) in the “Replace with” field box. Then, click on “Replace All”.
All the full stops will be converted to a slash character and Excel will now recognize the values as dates.
If however the spreadsheet data is one that is changing frequently, and you want to change this automatically, you will have to use the SUBSTITUTE function.
=VALUE(SUBSTITUTE(A2,”.”,”/”)) |
The SUBSTITUTE function is a text function, that means that it cannot convert to a text on its own. The VALUE function will convert the text value to a numeral value.
See the results below. It is required that the value be formatted as a date.
You can change the formatting when you use the “Number Format” list on the “Home” tab.
The use of full stop as a delimiter is usual. However, you can apply the same method to replace or substitute or replace any delimiter character.
Converting the yyyyMmDd Format
If you get dates in the format as viewed below, you will have to apply a separate approach.
This format is the standard format for dates as it takes away any form of vagueness regarding the way different countries store their dates. But, Excel will not understand it at first.
If you want to resolve this quickly, you can make use of Text to Columns.
Select the range of values that you want to convert, click Data and then Text to Columns.
The Text to Columns wizard will appear, then, you click “Next” on the first two steps taking you to step three (see the image below). Choose Date and select the date format being used in the cells from the list. For this tutorial we will be using the YMD format.
If you want to use a formula solution, you can employ the use of Date function to construct the date.
This will be used side by side with the text functions Left, Mid and Right to draw out the three parts of a date (day, month, year) from the cell contents.
The formula below is a formula making use of our sample data.
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)) |
If you employ any of these strategies, you can convert any number with eight digits to dates. For example, you might receive the date in a ddmmyy format or a mmddyyyy format.
DATEVALUE and VALUE Functions
In some cases, the problem is not caused by a delimiter character, rather, it can be that the date structure is strange because it was stored as a text.
Down here you will find a list of dates with different structures, although we may recognize them as dates, they were stored as texts and they need to be converted.
There are a number of ways to handle this conversion but for the purpose of these tutorial we will be making reference to tow of them. They are DATEVALUE and VALUE.
The DATEVALUE function converts text into a date value whereas the VALUE function converts text to a general numeral value. The difference between these two techniques are not much.
In the image that’s above, notice that one of the values has time data too. This more than explains how not so different the two techniques are.
The DATEVALUE that is below is able to convert each one to a date value.
=DATEVALUE(A2) |
Take note of how the time was removed from the result in the 4th row. All this formula does is to return the value to a date. The formatting as a date will still be done by the analyst.
The formula below uses the VALUE function.
=VALUE(A2) |
The formula will give the same results with the exception of the 4th row where the value of time is retained. The results can be formatted as date and time, or as date only to hide the time, but it will not remove the time.