8. Convert Dates from Text into Values
Dates and times will often be stored as text in a dataset. So that we can use these data in formulas, we'll need to convert into a date value or a time value using some Excel functions.
DATEVALUE: Converts a date (in text format) into a date value
ALT + I + C: Insert column
ALT + O, C, W: Manually widen a column
CTRL + 1: Open Format Cells dialog box
SHIFT + →: Select adjacent cells
CTRL + A: Select all cells within data region
ALT + E, S, V: Paste values
CTRL + SPACE + →: Select column
ALT + E, D: Delete selected column(s)
ALT + O, C, A: Autofit column width
Sometimes when we open a data set the date details such as day, month and year, can be stored as text rather than values.
From the three day columns in this data set the day and year are stored as values and aligned on the right, but the months are aligned on the left and stored as text.
So that we can perform calculations on dates, we'll need to convert this information back into a date value.
To do this Excel gives us a very useful function called DATEVALUE.
This function accepts a string of text that contains the year, month and day, and then converts it into a date value.
To implement this change for our current date details, we'll first need to combine the three columns into one date string, and we'll do this using the ampersand sign.
So I'll first create a new column with Alt IC, I'll call it Date, and manually extend its width with Alt OCW, and we'll make it 20.
We'll now combine the contents of the day, month and year columns.
So I'll write equals, and first select the day, then I'll write the ampersand sign, and then open double quotes and write a space.
Then another ampersand sign, and I'll include the month value.
Then another ampersand sign, and a space and double quotes, and finally one last ampersand sign and the year.
And now you can see that we have the full date in one column.
Now I'll create another column, and here we'll pass the date strings into the DATEVALUE function.
So I'll write equals DATEVALUE, open a bracket and pass in our text string.
Then I'll press enter, and now you can see that we have a date value, albeit in the general format.
Let's change this to a date format, so Ctrl 1 to open up the format cells dialogue box, and I'll select the date drop down category.
This format looks fine, so I'll press OK, and now you can see that we've changed this text string into a date format.
Let's select both cells and auto fill by double clicking in the bottom right hand corner.
I'd now like to remove the redundant columns, but unfortunately these values feed into the formula of our date value.
So I'll copy the new date values with Ctrl Shift down arrow, and Ctrl C to copy, and then I'll paste values to remove the formula.
And now I can delete these columns without affecting my new date value, Alt ED to delete.
I'll finish up by auto fitting the width of this column with Alt OCA.
Our user signup dates are now stored as values, and we can begin to perform some time based analysis on the data set.
As you can probably guess, Excel offers a time value function if the time data in your data set is stored as text.
This function works in exactly the same way as the date value function.