All language subtitles for 2. Applying Custom Date Formats

So now we understand how dates and times are stored in Excel.

Let's move on to taking a bit of a closer look at custom formatting, and I'm also going to show you

just a couple of little really useful keyboard shortcuts for quickly applying some common date functions.

So if we start at the top of this spreadsheet, you can.

Still, I have a little table here.

It says date, time, date and time.

Now I'm going to show you a couple of functions here, which are a very quick way of inputting today's

date and today's time.

And I'm going to show you a couple of different methods.

One dynamic and one not.

Now, if you want to put today's date into a cell, there is a formula to help us do that.

And that formula is simply called today.

Now, the today formula doesn't have any arguments, but we do still need to have an open bracket and

a close bracket on the end.

Now, if I answer, it's going to show me today's date.

Now, when you are using this formula, this is a dynamic formula, which means it's going to update

according to your system time.

So if I was to open this worksheet tomorrow, that date is going to have changed to tomorrow's date.

Now, more often than not, that's super useful because it means you're not having to update it every

single day.

But there are occasions where you might want to hard code that day into a cell.

So, for example, in the accounting industry a lot of time there is a need to have a historical day

in the spreadsheet so we can still use the today function for that.

But we need to use the keyboard shortcuts as opposed to typing the function in.

So if I want to hard code into the cell today's date, meaning it's never going to change the keyboard

shortcut is control semicolon.

Now I have similar functions for the time as well.

So if I want to input into a cell the current time which updates automatically depending on the day

and the current time, I would use the function called now.

And again, this is a function that has no argument, so we can simply inputs it, and it's going to

give us the date and the time.

If I want to hard code the time into a spreadsheet so that it doesn't change again, we can use a keyboard

shortcut control shift semicolon.

Now, if I want to do hard code both the date and the time into the spreadsheet, I can just combine

those keyboard shortcuts.

So I would enter the date, first of all, control semicolon space and then the time control shift,

semicolon and enter.

Now what about if I wanted to change the formatting on these dates and times, for example, the date

that we have at the top here?

Currently, I have date formatting selected and I'm using short date format.

Now we've already seen how we could change that to long date format if we wanted to, simply by selecting

that option.

But we do have more options underneath the Custom tab in formatting now.

To get to custom, you can either click the dropdown and go to more number formats at the bottom.

Alternatively, you can click on the little diagonal arrow in the corner and go to the Number tab a

right at the bottom.

Here we have a custom section.

And this is really where you can customize the formatting that's supplied to the cell and you can really

go to town here.

Now it's worth noting that these aren't just for dates and times.

You can customize formatting for pretty much anything that you have in a cell, but there is a little

group down here specifically related to dates and times.

Now, if we click on this first one, you can see the format.

There is month, day and year.

And in the sample just above, it's going to show us how that's going to affect the value that we have

in the selected cell.

If I click on the one below, that's going to change it to a completely different format.

So now we have eight, Jan. 22.

The next one down is just eight Jan, the next one down Jan. 22.

And you'll notice these are kind of set out as little pieces of code.

So the D stands for the day, the M stands for the month and the Y stands for the year.

And how many of these you type in changes the format of this date.

So if I decided I wanted this format where we have the word Jan, which equates to three M's, if I

wanted that say Jan 2022, I don't have a preset option for that, but I could change it up here now

because I just have to wise, it's given me the last two characters of the year.

If I add another y, take a look at what happens just above, I get the full year if I add another one,

if that makes more sense to you.

It's.

In a stay the same thing, so you can completely customize how your formatting looks, let's click on

OK.

I could do the same for the date and the time.

Now I think in this one, I'm just going to add to the time.

So let's just do control shift semicolon.

So we just have the time in here.

Now my time is currently displaying as 11:38 pm, but again, I could jump into custom and I could use

one of these time options down here.

So if I want to display in a 24 hour clock, I have an option for that.

If I want to display the seconds, I can do that.

So on and so forth.

So I think I'm going to display it using the 24 hour clock and click on OK, let's apply some more custom

formats to our invoice tracker table below.

Now I'm going to select all of these dates, and this time I'm going to click the dropdown more number

formats and that's go to custom.

So what about if I want to display this date as zero one zero for 2020?

Well, let me have a quick look through some of these.

I don't think I have one that looks exactly as I want it to look.

So what I'm going to do is I'm going to go up to this type field.

I'm going to add in another and see what that does.

Hmm.

That's exactly right.

Zero one is displaying now.

So if I go to D and add another D, I get the zero four.

And if I just wanted it to say zero one zero for 20, I could delete out two of these y's and click

on OK, and I've completely customize that formatting.

Now, custom formatting can get a lot more complex than what we've done just there.

But in terms of this intermediate course, that's pretty much all we are going to need to know at this

stage.

So jump into custom formatting, have a little play around with some dates and times, and really make

sure you understand how you can modify the formatting so that your values look exactly the way you want

them to look.