[SOUND] Hi, Nicky. >> Hi. >> So last week, we manipulated and corrected all our text data. Is it ready for analysis now? >> Not quite, we've only dealt with text. We still have some problems with some of the numbers and the dates. So we're going to need to look at fixing those two. >> And because text functions are only designed for working with text data, I'm guessing there will be a whole collection of new functions for doing this. >> You are so right. First up, we're going to look at functions for converting data types, so we're going to convert text to numbers. We're going to convert dates to text. And then we're going to look at how we can convert numbers and text to dates. And this will again help solve the problem of data not being in the correct format. >> I've had that same problem. I downloaded my bank statement, but when I tried to add up all the credits and the debits, I got zero. And it turned out that even though they looked like numbers, they were actually text. >> Yeah, and that's a great example, and exactly the kind of issue we're going to look at fixing. >> I've also had some strange things happen with numbers and dates. Sometimes I type in a date, and I get a random currency value. And other times, I type in a number, and it converts it to a date. >> That's quite alarming, but it's not Excel gone mad. The reason is that dates in Excel are really just numbers formatted to look like dates. >> What do you mean when you say the data is formatted? >> Formatting means change the way something looks, but not changing its actual value. So for example, 0.5 is the same as 50 cents, or it's 50%. We've just formatted it to look different, but it's actually the same amount. Dates work very much the same way. The number 1 is the same as 1st of January, 1900. 2 is the 2nd of January, 1900. And so today's date would be equivalent to however many days it has been since the 1st of January, 1900. >> So that means we can't have a date in Excel before the 1st of January, 1900. >> Yeah, exactly. But more importantly, it means we can do arithmetic with dates, like we would with numbers. If we take the 1st of Jan, and we add 1, we get the 2nd of Jan. Now, that's really useful. And that's the other thing we're going to be looking at this week, performing calculations with our dates. >> That sounds nice and straightforward. But what about if you want to take into account weekends, and holidays, or try and work out the last day of the month? Which isn't the same for every month, and isn't even the same every year for February? >> Yeah, and that's where Excel has some really great functions, specifically for solving those trickier date problems. So we're going to be looking at WORKDAY, WORKDAY international, EDATE, and the EOMONTH function, which will solve that last problem you mentioned. >> Thanks, Nicky. So another action-packed week. Here are the topics we'll be covering, converting data types, understanding dates and basic date functions. Generating valid dates, calculating days between two date,s calculating dates from a given date. Each one has a great training video. Don't forget to download the new workbook for this week, as it's a little bit different to last week. And check out this week's toolbox, which has another quick reference guide, and some really useful bonus material, which will help you with this week's practice challenge. Everyone say wow.