Welcome back to our course on Excel 2016 Advanced.
In this section we’re going to look at some of the most important aspects of using Date
and Time Functions in Excel 2016.
We’re not going to look in detail at formatting.
I’m going to assume you’re pretty familiar with date and time formatting, although there
will be various examples coming up throughout the course.
But I want to talk about actual date and time processing first.
We’ve looked at storage in the preceding section.
And as a first step in this I want to look at one of the examples we’re going to be
developing as we go through the course and that is the creation of a company invoice
in Excel.
Now let’s suppose that I put an invoice date on my invoice and in fact there are two
dates associated with the invoice.
There’s the invoice date and the due date.
Now first of all one way that we could populate the invoice date field will be to use the
Today function.
And the Today function gives the date today.
Note, Function Arguments, it doesn’t need any argument.
So that’s all I need, click on OK there and I have today’s dates.
Obviously I could change that format if I wanted to.
Now the due date would be potentially a date a certain number of days beyond that date.
And one way of doing this would be say well the due date is say always 30 days after the
invoice date.
How do you find a date 30 days after the invoice date?
Well remember the invoice date will be stored as a number of days.
So in fact all I really need to do is put in here =C2+30 and that will give me a date
30 days beyond the invoice date, which in this case will be September the 10th.
As we work through the balance of the course we’re going to continue to develop this
invoice.
But there’s still quite a few points to make here about dates and times and the first
of them is that if I click in C2 the definition of the date there is that it is today.
And of course if I close this workbook and open it tomorrow the date in there would be
tomorrow’s date because it’s always going to show the current date.
Now if in fact what you wanted was to put there a static date which may well be the
case with something like an invoice then the way that you put in a static date is not to
use a function but to either type the date in as a fixed date or you can use the keyboard
shortcut Control-Semicolon and that puts in as a static value the current date.
Now in the case of something like an invoice it’s quite possible that on some occasions
you might want to have a fixed date.
You might also have a fixed date say if in an Excel workbook you were recording the dates
of some holidays, something like that.
Now apart from the Today function which gives you the current date there is also the Now
function.
And the Now function gives you the date and time now.
Note the usual string of hashes because this cell is not wide enough.
That’s better.
So that’s the current date and time.
But there is also a keyboard shortcut that you can use for the current time and that
is Control-Shift-Semicolon which gives you the current time.
Again, E2 is dynamic, F2 is static.
So having looked at Today and Now let’s look at another important function.
Let’s suppose that you’re importing a load of data from another system and what
you have is several dates such as this one in C6 but they are text dates.
You’ve got them in an Excel workbook but they’re text.
How do you make them into dates?
i.e., so that you can store them as regular dates in Excel 2016?
The Function that you use for that is the Date Value Function.
So I’m going up to Date and Time.
I’m going for Date Value and I’m going to select C6, click on OK and of course I
have 41,860.
Note that although I’m using U.S. locale I’ve typed in a date which is in a non-U.S.
locale, intended to be a UK locale.
That doesn’t generally cause Excel 2016 a problem.
It can figure that out for itself.
And if I now applied Date Formatting to D6 what I get of course is August the 9th 2014.
The next set of Date and Time Functions that I’d like to look at is a set of six functions
that can separate out for me the year number, the month number, the day number, down to
the second number of any given date and time.
I’m going to use the Date and Time in E2 and just bear in mind for a moment that a
minute or two has passed since E2 was last updated.
So it’s actually slightly out of date at the moment.
But if in F4 I use the Year function and then when the Function Arguments dialog comes up
I choose E2 a couple of things happen.
First of all there would effectively be a refresh on E2.
But also at whatever point that happens the year number will be shown in F4.
Now of course in the time it takes me to do this the year number won’t be changed.
It’ll still be 2016.
So let’s click on OK and there it is, 2016.
Let me do the same with the month number.
I’ll do the next couple.
Now when it comes to minute a small amount of time has passed since E2 was last refreshed.
It’s currently 18:15 according to that.
E2 is now the argument for the minute function.
Click on OK.
E2 gets updated to show 18:16.
And of course the number that appears in F8 is 16 as well.
And then the final one of these six functions is the second function.
So those six functions can be used to isolate year number, month number, day number, hour
number, minute number, second number.
And one very useful thing to be aware of here is that if you look at the value in E2 currently
showing today’s date and the time of 18:25 which is provided by the Now function but
in fact that time has passed.
Any change to the cell values anywhere on the sheet will cause that value to be updated.
So the Now function, and in fact the Today function which is also used on this sheet,
their values will always be updated when I make any change to data in the sheet.
Now look at the values in F8 and F9, so that’s minutes and seconds for the date and time
in E2.
Even if I just say type a one in E6, just hit the down arrow.
You see that the minutes and seconds are updated.
Of course everything is updated but only those two values changed in the time it took me
to do that.
Now I’ve already shown you how to get a date from a text date.
There is another way that you can build dates as well.
And in fact you can use a similar approach for times.
And that is to use the Date function.
Now what the Date function does is to build a date from three constituent parts.
Now I’m going to put a use of date in here in D8.
And what I’m going to do first of all is to invoke the Date function.
So from Date and Time, Date.
I’m going to put in a year number.
I’m going to choose 2017.
I’m going to choose a month number.
I’m going to make that 11 and a date of say the 16th.
So that should be November the 16th 2017.
Click on OK and it is.
Now I don’t have to use constant values for those three arguments.
I could in fact use values from other cells on the worksheet.
So for instance, instead of 2017 I could say the year from D2.
I could say from D2 and in fact the day from D2 and you should not be surprised to see
the date I finish up with is the same as the date in D2.
And in fact this is an alternative approach.
For example, to adding 30 days to the date in D2.
So all I would need to do there within the Date function third argument is to put +30.
And that gives me the same result as I would expect, September the 10th.
Now one of the advantages that this approach has is that I can apply the same principle
not just to days but I could, for example, add three months.
So what would a date three months beyond the date in D2 be?
D2 has got August the 11th.
Yes, it would be November the 11th.
Now let me change the date in D2.
It’s currently August the 11th.
Let me make it August the 31st.
Watch what happens.
In adding three months to that date Excel is not fooled because it adds three months
to August the 31st.
It gets November the 31st.
Of course there’s no such date so it makes it December the 1st.
That’s a pretty good function to use sometimes as well.
So we’ve looked at several very important Date and Time functions in this section.
There are two or three others that I need to go through with you and we’re going to
cover those in the next section.
But I’ve saved this particular workbook in the course files folder as Date Functions
01 in case you want to go in and play around with it a little bit yourself.
I know there’s not an awful lot in it but some people find it useful even to get quite
basic workbooks like this so that you can actually manipulate the values yourself.
I’ll see you in the next section.