How to use Date and Time Functions in Microsoft Excel 2013
The date and time functions in Excel enable for precise tracking of order creation dates as well as due dates for various business applications. When looking at invoice due dates, business owners can use Excel to automatically track and manage order due dates.
This eliminates potential error when submitting invoices to clients. Excel tracks the number of days since a particular date and then calculates when the invoice would come due.
As far as features go, this one is obligatory, enabling easy and exact tracking of all sorts of time sensitive information.
Watch the free video here, transcripts for the entire video follow:
Learn how to master Microsoft Excel 2013. Get 19 hours of Microsoft Excel 2013 training – CLICK HERE.
Video transcripts:
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.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
