All language subtitles for 1. Understanding How Dates are Stored in Excel

In this section of the calls for going to delve into date and time in a little bit more detail, we're

going to take a look at some date and time functions a bit later on in this section.

But in this introductory lesson, I just want to make sure that we all understand the theory behind

dates and times in Excel because it does work a little bit different.

So on this worksheet, I have a small table with three column headings short dates, long date and time.

Now, currently, I don't have any date or time formatting applied to these numbers, and this is the

first rule when it comes to dates and times in Excel.

They are essentially just plain numbers underneath.

They become dates and times when we apply the appropriate formatting.

So if you've ever received a spreadsheet from a colleague or a client and you've looked in the date

column and you see numbers which look something similar to this and you've wondered why those were there,

it's because this column doesn't have date formatting applied to it.

If I take a look up on the home ribbon in the number group, you can see that currently I just have

general formatting applied.

So whilst these are dates, they're not displaying correctly because I don't have date formatting applied.

Now what exactly are these numbers?

Well, the way the Excel works is that it takes the first of the first 9500 as day number one and all

days are numbered from that point, respectively.

So the numbers that you see in here are basically the number of days past the 1st of January 9500.

And if you have incorrect formatting applied to date cells, you're going to see that underlying number

as opposed to the actual date.

Now notice if I select this column of numbers, go to my numbering dropdown.

I have two options in here short date or long date.

So let's start out by applying short date format and now those look a little bit better.

We could do a similar thing, but apply a long date format as well.

So we have those same numbers.

Let's go to the dropdown and we can apply long date format.

I need to make this column a little bit wider by double clicking, and now I get the longer version

of the date.

And there are tons of different date and time formats that we can use, which are going to take more

of a look at in the next lesson.

Now, in third column, just here we have some times and things look a little bit different from the

dates, but they are effectively still numbers.

But the difference with times is that when we don't have time formatting applied, which we don't in

this column, we just have general formatting applied again.

All times are a fraction of one.

So let me show you what I mean by that.

I've got this little fractions column just here, and I've just added some numbers.

We have zero then point to five point three point four point five all the way up to one.

Now, if I just select these and let's just make a copy of those cells if I apply time formatting to

these.

Like so you're going to see what those fractions equate to.

So 12 am, according to Excel, is zero point two five, which is essentially a quarter of a 24 hour

period.

To excel is six am point three is seven twelve point five, so half way through the day is 12 pm.

And then when we get to number one, we're back to 12 am.

So Excel stores times as values between zero and one.

They're effectively fractions.

So again, if you see a spreadsheet that has times that looks something like this, this is what those

numbers represent.

Now let's apply the correct formatting to these as well.

I'm going to press control, shift down Arrow to select all of the data up to the dropdown, and this

time we're going to use time.

And now those times look a little bit better.

So that is the basis of how Excel stores dates and times in your spreadsheet.

They're stored as values and we get them to look like dates and times by applying the correct formatting.