Hello again and welcome back to our course on Excel 2013.
We’re currently looking at the use of functions in Excel 2013 and we’re looking at the use
of functions to improve the generation of invoices for this plumbing company.
Now in the previous section, we used the VLOOKUP function to use what was effectively a customer
database on the Customers Sheet here.
So let’s just take another look at the Customers Sheet and what I’m going to do now is to
make a change to this database.
You’ll very often be in the position that you need to make changes, and the change that
I’m going to make here is, first of all, I’m going to change the terms column.
Currently the terms say things like 7 days, 30 days, 30 days, 60 days.
I’m going to change the way that that column works by just specifying only the number of
days and not say 7 days, 30 days, 30 days, and so on.
So for instance, in that one I’m going to change that.
I’m just going to delete the word Days.
I’ll delete the rest of those and then rejoin you.
Now, first of all, why have I done that?
Well, the reason I’ve done that is that I want to be able to calculate when an invoice
is due.
And the way I’m going to calculate it is by adding the number of days in the terms
of business for the individual company that I’m dealing with to the date that the invoice
was generated.
So for instance to find when an invoice to Frequent Showers is due, I would add 7 days
to the invoice date.
Now, of course, that will have a consequential problem.
If I go back to the invoice itself where it says Terms, it will now just say 7 instead
of 7 days.
And what I’m going to do now is show you the use of what are called Text Functions
to put text or extract text or change text in any field.
Now I should point out here as I have done from time to time that there a few ways of
doing this and I’m only showing you one of the available ways of doing it.
But if you select the cell, in this case the one that should say 7 days and just says 7,
we’ve still got the formula in there that we put in before with the VLOOKUP function.
What I really want to do is to sort of attach the word Days to the end of that in some way.
Now one of the ways of doing it is to use one of the text functions.
And within the Function Library on the Formulas tab, text functions have their own little
category.
There are many of them.
And I’m going to use the one that’s called Concatenate and concatenate joins several
text strings into one text string.
Now all I need to do to use concatenate is to put brackets after it and then to put the
text strings I want joined together in the brackets separated by commas.
Now this is where sometimes people start getting a bit confused because you see an awful lot
of brackets appearing.
So I’m going to take this step by step.
First of all, equals in the formula bar says this is a formula.
And the main part of the formula now is concatenate.
Now note that I could have used that from the drop down and it would’ve put in if
you like the bones of the function for me and let me put in the rest of it myself.
But I’m typing it in directly as concatenate, then I’m opening brackets, and it will tell
me now that let’s have the first bit of text.
Now my first bit of text is what that VLOOKUP does because what that VLOOKUP does is to
give me the number of days.
So if I just had that, I’d get 7 or 30 or 60.
But I don’t just want that.
I want another piece of text as well.
Now the other piece of text is always the same and it’s almost this.
If you want to specifically put a piece of text in put double quotes and then put the
word, in this case Days because I want it to say 7 days or 15 days or 30 days.
One slight problem there though is I also want a space before the word Days.
So it will 7, space, Days or 30, space, Days, etc.
Now when I’ve put in all the strings that I want I put in, close the round bracket,
and I will enter that as my formula.
And now look what happens, it again says 7 days.
And if I change, let’s try a different account, 2973, tick that.
It says 30 days.
So that’s great.
So I mentioned just now that the idea of introducing the number of days rather than the whole phrase
was to enable me to put a due date on to an invoice and that’s what I’m going to do
in the next section.
I’ll see you then.