Join Dennis Taylor for an in-depth discussion in this video Use the DATEVALUE and TIMEVALUE functions, part of Excel 2016: Cleaning Up Your Data.
- [Instructor] On this worksheet called 'Date Value' we're seeing some things in Column A that look like dates and they look like valid dates. In other words, as I click over here in a different cell If I were to type in: J A N space 1 comma space 2 0 1 2 just the way we see it over in cell A2 I press Enter what happens? Excel displays that as a date. Now, might not be in the format that I want I could from the Home tab go here choose Short Date that's the way I want it.
More important than the actual format though is the fact that this is treated as a date we can work with it. Can we work with that date over in Column A as a date? Now, if I write a formula here equal this entry maybe I add 1 to it. Somehow it rather looks as if Excel is trying to use that but that's not a workable number could I format it as a date? Back on my Home tab the drop-arrow here in the Number group let's choose Short Date. We're in shape. So we possibly could've done it that way.
But a faster way to clean this up is use a function called Date Value. If you've run the situations where you got dates that have somehow been treated as text or converted into text and that happens sometimes when you're copying data through various interim software packages or somewhere along the line somebody moves into this workbook or that workbook you're not always sure. Date Value. I'm going to click cell A2. Because the other cells are highlighted I can complete all these at once.
Not by a simple Enter but Control Enter. And we have this. Now that doesn't look like it's valid but from the Home tab again; Number group here we can convert these to a Short Date display. Now, to make this a little bit more complex and that's not my goal here of course is. This is workable we now have dates here that we can work with. However, they are formulas we see that they are formulas. If we convert these to values it's going to convert them back into those numbers again. Which by the way are accurate but we don't want to see them so I have to take two steps here.
In other words, we don't want the formulas here. We want to get rid of the formulas keep the results. What do we do? Fastest way to do this is by using the Right mouse button Copy this either into another column or drag it right and then right back on top of itself with the Right mouse button and the choose 'Copy Here as Values Only'. And there it is. So these as we see them here are converted. We don't need the data over here anymore we've got dates we can work with.
Now, same thing that might happen with times. Could we add an hour to this? That's 1/24th of a day. That looks like potentially a viable number. Back on my Home tab, drop-arrow let's look at Time that's workable so that's one technique. Again, it could be a bit faster and a bit more direct to use a function a companion to Date Value called Time Value. And it too is designed to work with unusual formats.
Now what we're seeing in A13 looks like it could be valid but it too might be a text function so let's click over there and here press Control Enter since we got two cells highlighted and we got something going on there that looks a little strange. Here too what we might do is highlight both of these choose Time for both of them and there we are. Sometimes again in the copying of cells back and forth formats get copied. If those are not to your liking – the formats keep in mind that anytime working with dates or times you can easily jump into Format Cells Control 1 is one fast way.
And in this case it takes us to the Time category we probably want to use either AM/PM a provision right here without showing seconds as we're now seeing in the display that's one. And one other one as we go back if we want to see this in 24-hour style time use the option 13:30. But in any case converting dates that have been entered as text or converted to text somehow we want to convert those into dates that are workable same situation with time we use either the function Date Value or Time Value to clean up that information.
Lynda.com is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Moving and inserting rows and columns of data with a simple drag
- Replacing data at the character level
- Converting dates with text functions
- Converting text data to values
- Using the CONCAT and TEXTJOIN functions to combine data
- Splitting data into columns via the Text to Columns feature
- Using Flash Fill for faster combining and splitting
- Checking and correcting spelling mistakes