hi guys this is Mina. Today i'm going to show you how to create
date table in microsoft excel. so to create my date table, i'm going to
have the following columns my date key
the actual date
i also have the year so these are all the dates columns that
i'm gonna have in my table and i'm going to populate them using
excel formulas. you can also decide what other date
columns you want to add to your table. To begin with, i'm going to create my
date key and i'm going to create this date table
starting from first january 2019 to the 1st December
2020 and i always prefer my date key to be consistent with my actual
date so for instance since my date is going
to start from january 1st 2019 i'm going to
make my date key the first part is my month
so 0 1 and then the second is the day and that's also 0 1 and 2019.
so month day 2019 after creating the first two rows you
can drag down to populate the rest
for that month and then start all over again. so
for the month of February i have 02 two 01 2019
and then 02 02 2019
hold the first two rows and drag down to the last date
of the month of february so i'm done populating my date key
and i can press ctrl plus the down arrow to get to the last date
key which is december 31st 2020 next i'm going to
type in my actual date which is 1st of January
2019 again after creating the first two rows you can
drag down so i'm just going to hold this first two rows and drag down
okay so i have my actual date populated ctrl and down arrow. Next i'm going to
use the formula year of
select year of my actual date enter 2019. okay so i can just double
click here and i have 2019 so again if i press
ctrl and down arrow it should take me to the last
date on my table which is the year 2020. to find the month number of the year i'm
going to use the formula month of my actual date so i have
april is the fourth month of the year and so on to create day number of the
month it's also going to be day of my
actual date and then double click to drag there there is a function in
excel called week number and that's what we are going to
use to fill this column week num okay of my actual
date and that should give us the correct week number of the
year
okay good use a function called weekday
to find day number of the week so weekday of my actual date
it gives me this answer okay so i expect to have a maximum of
seven days a week so this is
up to seven and then it starts again seems to be working
correctly. Day number of the year would just be
from one two three downwards we expect to have three hundred and
sixty-five days a year so after each year ends, you
need to start all over again from one so you can make sure you
have the right day number of the year and
there we go. Using the formula text
of my actual date and then d d d d
should give me the day name of the week okay so i'm just just gonna
double click here and um so saturday is the last day of the week
and then starts again on sunday so day number of
the week is one and the day name is sunday
perfect. In the same way, if you want the month name it's going to be
text of my actual date and that's going to be
just as you guessed mmmm. Great so guys these are the columns that
i've created for our date today. There are some things that you need to
be careful about when you are creating a date
table. The first is the start date for your date
table has to be higher than a date in your transaction table and
also it has to end after the last date in the other
tables in your database also make sure that all the dates are
continuous you don't miss any date as you can see here i started
from january 1st i did not miss any dates they were all
continuous and there is no duplication of any day
at all in my date table. If you follow these two
rules you can create any date table that you
want and you can use it for your analysis in either power bi
or tableau or you can link it to your fact table in your data warehouse and
then you can slice and dice your data for further
analysis. okay so this brings us to the end of our
tutorial on creating a date table in excel. thank you so much for watching this
video and see you in my next one bye