Microsoft Excel 2013 Tutorial - 23 - Boring Date and Time Stuff Video Lecture | Microsoft Excel 2013: Be the Excel Master - Software Development

all righty guys welcome back to another
video and as you can see by the title
I'm gonna start talking to you guys
about times and dates so one time I went
on a date with this chick and she had a
huge I'm just kidding not those kind of
dates and times I'm talking about the
type of data whenever we work with dates
such as birthdays in Excel so before I
get started showing you guys a bunch of
cool stuff that you can do we need to
understand one extremely important
concept and this doesn't only apply to
excel but also computers in general and
that's this computers have no concept of
time they're not human beings all the
computer is it's pretty much just a
really fancy and actually really
powerful calculator so computers excel
especially is really good at working
with numbers crunching numbers but
behind the scenes whenever we enter a
date it's always taking that date and
converting it to a number so even when
we you know type some time in here
eventually I'll show you guys at
different ways that you can you know
format your time but it's always gonna
convert it to a number so remember that
and actually this is kind of confusing
at first because you're like alright if
it's taking the dates I type in and
always changing it some random number
that it can work with it's probably
gonna be really confusing but it's not
because whenever you want to have
perform calculations like subtracting
dates from one another for example if
you want to calculate the age of
something you would subtract two days
from one another it it makes a lot of
sense once you understand that concept
so enough about you me rambling on I
sound like kind of a some kind of hippie
talking about time for the last two
minutes but let's go ahead and get
started and just keep that in the back
of your head because you'll see when it
comes into play later on so the first
thing that we have to take note of is if
computers don't have any sense of time
then when did time begin from this
computer well whoever made excel they
just picked a random point in time so
whenever you type one
as a date this is January 1st 1901 means
in Excel and if you don't believe me
check this out
type one into a cell right click it and
hit format cells and convert it to a
date yeah okay and look at what it does
1 1 1900 so remember the number one
always resembles January 1st 1900 not 3
if we did the same thing convert that to
a date as well this is January 3rd 1900
so once the first 3 is the third so on
and so forth so that's pretty much how
Excel is going to handle dates each
integer starting in January 1st 1900
resembling one day now if you ever want
to enter to the current date then what
you can do is you can hold down control
on your keyboard in this then press the
semicolon and it enters the current day
so today whenever I'm making this
tutorial it's 11 5 2014 it's actually
252 a.m. because I couldn't sleep and I
was like you know it might as well make
some stinking tutorials so that is the
current date and if you guys were
curious if you ever want to know what
this is in Excel time let's go ahead and
convert that to a number so format cells
I really wish that pop up on my screen
and let's just convert that to a number
and hit OK so according to excel but day
is 4:40 1948 days now what this means is
since January 1st 1940 1948 days has
passed now another interesting thing
that I want to point out is drag this
down just so I have a copy is this you
see that Excel also throws in a decimal
place whenever it's working with dates
well if you're wondering what's the
point of that kind of decimal point
that's move what's the point that was
probably a joke in there somewhere but
I'll worry about it later
whenever you have a fraction of a day
this is how Excel treats time since
that's pretty much what time is like
noon that would be half a day 12 hours
so if I ever did something like I just
do um I'll just show you guys noon so
whenever we want to work with time it's
4 1 9 4 8.5 and actually let me copy
that over so this might be a better
demonstration
so let me format this to it I'll just do
it to a day with a time so check this
out
so whenever we have half a day it means
literally half the day as in 12 hours if
we ever were to do something like like
0.75 that would of course be 18 hours 12
plus 6 that's how you come up with that
now one more boring thing that I need to
talk to you guys about before we start
learning about the cool day in time
functions is one other weird rule or
it's not really a rule it's just
something weird that Excel does that is
not intuitive at all and it's probably
gonna confuse you guys if you ever see
and that's this so type in some date
like I'll type in Christmas so 1225 I'll
say it's like um I don't know 84 oops
so 1225 84 so Christmas in 1984 hit
enter all right it filled in that 19 for
us they knew we were talking about in
1984 pretty cool but now I want to enter
a date Christmas so 1225 um let's say
we're working with 1917 so I just typed
17 in there and hit enter what the heck
all right so I just hit 84 here and it
changed it to 1984 but when I hit 17 it
changes it to 2017 so when do I know
okay is it gonna put 19 or 2,000 um it
actually this is one of the weird things
Excel does whenever you're entering the
Year always right it is 4
digits pretty much because Excel is
weird and I think that threshold is 30
anything above 30 then it's going to
convert to 1900 anything below 30 it's
going to convert to a 2008 so again one
other thing if you're like trying to
autofill stuff and it's given you the
wrong data make sure you always use four
digits four years so now that we got all
the stupid stuff out of the way like I
said in the next tutorial we're gonna be
learning about the useful stuff you can
do with dates and times kind of
calculations and functions and awesome
stuff like that so thank guys for
watching don't forget to subscribe and
I'll see you guys next time