It so first and foremost I want to talk about something called the date value.
And if there's one lecture you're going to listen to or one slide that you're going to read in this
entire section this is the one to pay attention to and it's because the date value is the critical piece
that allows Excel to process dates and times.
So every date in fact every moment in time in Excel has an associated underlying numerical date value
.
That's how Excel.
Able to do things like calculate the passage of time or apply statistical functions to dates without
those date values.
Dates would kind of just be treated like text fields.
You wouldn't really be able to do much in terms of GNIP relation with those dates.
So midnight on January 1st 9300 is treated as I guess the beginning of time and excels universe.
So January 1st 9900 has an underlying date value of 1 and then every subsequent day beyond January 1st
9900 has a date value one number higher.
So January 2nd 9900 has a date value of 2.
And so on and so forth.
So Excel is actually really really smart about dates.
And then it recognizes most type dates and automatically apply as a common date format.
In most cases it will be month slash days last year with four digits along with its associated date
value.
Now the way to check if your entry is recognized as a date is to right click go into cell format and
switch to general and it will show you if there's an underlying date value.
So I'll show you an example of that right now.
If I jump in and just a blank workbook here I'm going to type in a date by hand I'm going to do January
1st 2015 press enter and never right click a one go into format cells as you can see it defaults the
date field.
This is the default version day month year.
But if I switch to the general category you can see that it has an underlying value of 42000 5 because
it's the day that's fallen.
Forty two thousand five days after January 1st 9300.
So that tells me that OK Excel has recognized my entry as a date I can treat it as such.
It's also pretty good about understanding kind of strangely formatted ones I could say like oh one dash
one dash 15 and you can see that it's switched it over to the default version because it knew I was
trying to type a date and you can actually get like a little bit weird with it.
You know I like to kind of test to see how strange I can get.
So even that version where I typed it as you know dash that combination of dashes and slashes it still
recognize it as a date and it's still applied that date value to it.
So Excel is actually pretty smart when it comes to those things and that's kind of the core reason why
are able to do such interesting manipulations and calculations against dates.
So if you happen to type a date that Excel doesn't recognize one common example that I've seen is if
you type a series of numbers like date month day so for January 1st 2015 again if you typed 2015 0 1
0 1 with no dashes or slashes.
Excel would not recognize that as a date.
And so it wouldn't apply an underlying date value and it would treat it as a text string or in this
case value.
So if that's the case like if I download some data in the date field is in a very strange format like
that I'll show you how to use tools like date value or time value to convert those formatted dates into
formats that Excel understands.
So looking at some examples down here again January 1st 9300 is Date 1.
So date value equals 1 10 days later on the 11th and 9900 would have a date value of 11.
February 6th is 42000 41 days after January 1st 9300 and these de-valued values don't have to be whole
numbers.
In fact 42 0 forty one point five equates to noon on February 6th whereas 42 0 or forty one point seventy
five equates to 6 pm which is 75 percent of the way through the day.
So you can get very granular even down to the second.
And that's how you can start working with very particular times in Excel not just hold dates.
So hopefully that helps.
Once you understand this concept to date value suddenly everything becomes a bit more clear when we
start talking about actually manipulating and applying functions to these dates.