How to drag Years In Excel
Excel Video 166 DATE, YEAR, MONTH, and DAY
0:00 / 0:00
Quality:
Sharing buttons:
hi this is excel video 166 in more
solutioning com-series videos on how to
use Excel and medical practice just a
quick note if you don't see the blog
entries either through the MTA make so
use your community or on my blog you
might find those helpful if you go
through and watch these videos that I
try to explain what I'm doing in between
the videos and the blog hopefully learn
a little bit about how Excel works all
that said I want to do four really
really basic functions today but then
put them together to show you something
that's I think helpful ah let's start
with on some really basic stuff here
here's my date and there's a function
called year that will tell you the year
of what's in g1 a month will tell you
what month it is the day will tell you
what day it is and your really really
basic all you got to do is put the year
the month and the day and that works the
other thing I want to show you is this
date function and what date does let's
just pop this in is you tell it the year
you tell the month you tell today and
Excel will put it in a little date time
code number formula thing that I will
show six 15 2011 you're saying Nate why
on earth I mean I can look at a year
until what year it is I can look at the
month I mean I don't need a formula to
put in 6 15 2011 I can just type that
that's all true let me show you where I
use these types your month date kind of
things and this date that puts the year
than the month in the day together if
you have a list of months and you want
to chart or graph or report on a bunch
of months one of the things you can do
is notice how my cursor turns in this
plus you can drag down and Excel will
try to fill for you but you see it cells
not helping me here they're giving me
days and you know not what I really want
to do is do August but if I type in 8
111 and I type in 9 111 say and then I
take this range and fill down let's just
go to the Union now we can go through
then Excel will calculate that for me
and I can drag down and you don't have
to type all those things Excel just do
it for me another way to get these dates
is to take this whole range and then
this area over here is the fill button
and then you can do series and look at
the default is day like I had before and
I said no no I want month you can do
year you can do weekday if you did I
want every three months you could change
step value but that's what I want I just
want every month do another day and get
the same thing that way the other thing
I can do is I can do this formula right
here let me bring this up we're back to
that same date function again but look
what it does maybe if I drag it down
here I said give me the year from l4
which is right here so it says well yeah
I can figure that out it's 2011 then
take the month from l4 that's July and
add one so that gives me a and for the
day I'm just going to punch you in one
because I just want the first day of
every month when I copy this down I get
the same thing now I've got three
different ways to fill 12 months worth
of data and you say well you know if I
punch in a few I can drag down and that
work or if I do the whole list and do
fill down with the series that works and
there's some cool things we can do with
trends and stuff we'll get to the feel
series thing later but the thing I
wanted to show you is if in fact I even
put a little text box out here the only
way you can change the first date in the
formulas update automatically watch what
happen if I do nine one eleven here
nothing five nine one eleven year if I
put nine one eleven here see everything
update because it's all based on a
formula and so if you you want to look
at like a rolling report the last 12
months of collections of the last 12
months of receivables or whatever you're
looking at if you get the date right
once and then put the formulas in then
all you've got to do is update this date
and you're good to go and remember we
can even combine this with that now and
today thing we had a couple videos ago
and calculate what the day is and have
it all format for you so you never have
to do the series thing you never have to
do the drag down thing
works automatically because we're using
this date function and then year and
month and then understood in one foot a
bit that's what I wanted to show you
today some really basic stuff over here
but a cool way to make dates update
automatically by combining the formulas
together and do it something like this
hope that was helpful for you thanks for
watching
