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