Hello again and welcome back to our course on Excel 2013.
We’re looking at improving the generation of invoices for this plumbing supplies company
and in the previous section we looked at Text Functions to concatenate the number of days
in the terms of business for each customer with the literal string Days.
And with that, we’ve managed to put in here the terms of business for each customer.
Now one of the reasons for switching the way we did it originally was that we could then
calculate the due date for an invoice.
Now in order to do the calculation of the due date, we’re going to look in this section
at some Date and Time Functions.
Now the first thing I want to look at is the order date here.
The order date says December 1, 2012 and it’s actually typed in as a literal date.
What I’d like to do instead is to put in there the current date, the date today.
Now I’m on the Formulas tab on the Ribbon and there are the Date and Time Functions.
If I click on the drop down, again there’s a long list of them.
Two of them are of particular interest here.
One of them is Now and one of them is Today.
Now Today returns the current date formatted as a date, whereas Now returns the current
date and time.
Now I don’t need the time for an order date so I’m going to go for today.
Click on Today and that cell now contains equals today with brackets.
Now in the case of the Today Function, there are no Arguments and this particular message
here just reinforces the fact this function takes no arguments.
Click on OK and it will return today’s date which is December 11, 2012, so that’s fine.
Of course, you might worry, well if I look at this tomorrow will it say December the
12th?
But if I’m printing this and sending it to a client when it’s going to carry on
saying December the 11th if I print it today.
Now what I’m going to do is to basically put a due date on the invoice above the terms.
Now I’m going to cheat a little bit here because I’m going to copy the cell that
says Order Date and the cell that’s got today’s date in it.
So I’ve selected those two, keyboard shortcut Control-C to copy them, down here, paste them,
and I’m going to click on the cell that says Order Date and change that to Due Date.
And now the question is what do I need to do to this date in order to give a correct
due date?
Now the answer to that question is surprisingly straightforward.
And you may remember me mentioning much earlier on in the course that dates are stored as
the number of days since January 1, 1900.
So when you’re looking at a date like December 11, 2012, Excel actually just has a big number
in there.
It doesn’t actually store it as a date, it just has the number of days since January
1, 1900.
So if you wanted to find a date that was in this case 30 days later than that, all you’ve
got to do is to add the number 30 to the date in there.
So for this due date which currently says equals today, I can just say equals today
plus.
Now all I need to do is to add in the number of days in the terms of business for this
customer.
And this is, of course, my VLOOKUP function.
So let me come out of the one I’m editing there, go back into this one here, the 30
days.
That was the VLOOKUP part and what I can do here to save me typing that all in again is
in the formula bar, click to the left of VLOOKUP, just copy that as a piece of text, make sure
you get the last bracket, and then Control-C to copy it.
We’ve not made a change to that so we can just cancel that.
Go back in here.
So this is now the due date formula: equals today plus and now I can paste the VLOOKUP
function that I’ve just copied from the cell below.
Tick now, 30 days after December 11, 2012 is January 10, 2013.
That looks good to me, but I always test these things when I’ve done them.
So that’s client 2973.
Let’s try that with client 2971, tick that, 7 days.
That’ll be payable on December the 18th.
And let’s try it with 2974.
So I change the account there, 2974.
That’s 60 day terms.
That’ll be February 9, 2013.
So everything’s fine in terms of calculating these dates.
I’ll see you in the next section.