Original subtitles

Two other functions in Excel that can be super useful when you're trying to calculate dates are eaoh

month and a date.

So let's take a look at each month.

First of all, now eaoh month basically stands for end of month.

And in this example, on the screen, what I basically have here is a loan amortization schedule.

Now, don't worry too much if you're not sure what this is.

We are actually going to look in great detail how we put together one of these.

A bit later on in this course.

But basically what this does is it works out the monthly payments for a loan.

That's not particularly important in this example, but using the eaoh month function to work out the

payment date is.

So if we take a look at this table, this is basically our schedule table.

And what this tells me is how much I'm going to be paying each month, how much of that payment is going

to be interest, how much the principal loan, the loan amount effectively is being reduced by and then

the ending balance.

And what I want to add in here are the payment numbers and also the payment date.

And this loan is going to be paid on the last day of every single month.

Now, as for doing this, I just want to show you a couple of other little bonus tricks as well.

If I take a look at the table above.

Over the course of this loan, we're going to be making three hundred and sixty payments.

So effectively this is a 30 year loan.

So what I want in the payment number is I want numbers zero to 360 listed out here.

Now I could go through and start typing in zero one two, but that's obviously going to take me a long

time.

There are other ways I could do this.

I could type in the first few, say one to three.

I can select them all and then I can drag those down.

But still, dragging down to three hundred and sixty is a little bit tedious.

So a little trick that you might want to use if you need to get a lot of numbers into a spreadsheet.

Let's type in the first one.

I'm going to press control, enter to stay in the same cell.

I what I'm going to do is grab the little fill handle in the corner and using the right mouse click

button.

I'm going to click drag down a cell, drag back up a cell and let go.

And it opens up this secret menu.

And if I click series, I can then select columns and then choose my step value.

So we're going to step one each time.

Am I still value is 360 when I click on OK?

Take a look at that if I do control down Arrow.

Notice is input all those numbers all the way down to 360.

So that's a really neat little trick.

Now what I want to do is I want to enter in the dates for each of these payments.

Now again, if we look at the table at the top, the start date of the loan is October the 30 1st 2021.

So let's put that in as our first payment date.

So we want 10 31, 2021.

Now, if I want to fill all of these down, if I was to drag this date down.

Notice what it does.

It goes to the next day.

So we have October 30 1st, then November the 1st, November, the 2nd, November the 3rd.

So on and so forth.

Now I don't want it to do that.

What I want is for it to input the last day of the month.

So one of the ways that I could do this is to use the function a month.

So let's type in equals eaoh month.

The first thing we need to specify is a start date.

Now I'm just going to use this one up in the table D12.

And because I'm going to drag this down, I don't want this to move.

So let's lock it now.

This little screen tip is in the way so we can just track that down.

I now need to specify the month that I want to pull the end date from now if I was to put a zero in

here and close the bracket and enter.

It's going to give me October the thirty first.

Now I don't want that.

I want this one to be the end of November.

So let's double click.

And that's just what we have on the end here.

Let's change that to a one.

And there we go.

That's what I get.

So if you want it to impact the last day of the current month, you would use a zero.

If you wanted to input the last day of the next month, you would use one.

So on and so forth.

Now what happens if I try and drag this down?

It doesn't work.

Now, why is that?

Well, if we double click to take a look at this formula, it's because I'm using the date up here in

the table and I've locked it.

So it's always referring back to this date.

So really, what I should probably do here is if we delete that out.

I'm going to use the data above and hit enter.

And now if I double clicked copy that down, I should get the last date in every single month.

And if we take a look at February, it's picked up that February only has 28 days.

And it's also worth noting that if this is a leap year, exile does recognize Leap Year and we'll have

the 29th in there instead.

So each month, a really good formula for just listing out the last day of every month, let's move

across to the E!

Date worksheet.

Now this is kind of similar.

This is where you can start with the dates and then you can return a date a specified number of months

before or after that date.

So let's click in the cell and type in a date.

Oops.

Like so two arguments start dates and months.

So start date is going to be a five number of months.

Well, if I put two in here and close the bracket notice, it gives me a number.

And that's because I don't have these cells formatted as dates.

So let's deal with that straight away.

Let's jump up to here and change these to short dates.

And would you look at that?

It's now giving me a date that is two months ahead of the start date.

Let's do it again.

Dates, start dates, comma.

How many months this time we want?

Three.

Close the bracket.

Enter if I want 10, you get the idea.

Start date 10.

Now, what about if I want to go back?

Well, we can do pretty much the same thing, so we want a date, then we want the start date.

And what I can say here is minus one.

Close the bracket, enter and I get the month before.

Let's do it one more time.

A date start date.

And this time we're going back four months and hit enter.

And then, of course, I can grab all of these formulas in one go and copy them down.