Join Dennis Taylor for an in-depth discussion in this video Using the DATEVALUE and TIMEVALUE functions, part of Cleaning Up Your Excel 2010 Data.
If you have different kinds of date or time entries that are formatted as…text, you can't really directly reformat them all to be workable values.…So there are two special functions devised just for these situations.…One is called DATEVALUE; the other is called TIMEVALUE.…Look at the data in column A.…Now, I want to contrast that with what might happen; what if you would actually…type something in the cells that were not formatted as text?…I'm going to type Jan 1, 2010.…
It works just fine. And that--as we look in the formula bar, we can see this is an Excel date.…We can work with it in computational ways.…The data in column A looks like exactly what I typed in.…Well, let's just convert this in the cells below.…Let's convert it to dates.…Right-click > Format, and as we try one of the various formats, say, this one…here, it looks like one of the right path.…Let's just change the look of that so it looks like this.…When we click OK what happens? Nothing.…
So we've got a problem.…We want some control over this. How can we do this?…
- Moving or inserting rows and columns of data with a simple drag
- Using Text to Columns
- Harnessing the Find and Replace command to replace data at the character level
- Dealing with special characters and wildcards during search
- Converting dates with text functions
- Converting text data to values/numbers
- Checking and correcting spelling mistakes
- Splitting data into multiple columns via the Text to Columns feature
- Combining data from different columns via concatenation
Skill Level Intermediate
Q: Where can I learn more about Excel formulas?
A: Discover more on this topic by visiting Excel formulas on lynda.com.