馃攳
TECH-005 - Create a quick and simple Time Line (Gantt Chart) in Excel - YouTube
Channel: unknown
[1]
[Music]
[6]
hello everyone and welcome to the Excel
[8]
challenge if you want to learn how to
[11]
create a quick and simple timeline that
[13]
you can use in Excel and you can adjust
[16]
your task iterations and your start date
[20]
and end date and that sort of stuff
[22]
please stay tuned okay the first thing
[27]
I'm going to do is fill some general
[28]
information for project three green
[33]
project duration project start date and
[40]
project and be the name of the break is
[45]
going to be kitchen renovation I do top
[51]
now at this point the duration of the
[53]
prayer I do know we want to start on May
[55]
1st 2017 just a so formatting here
[65]
the next thing I want to do is start
[67]
plumbing my timeline here so I'm gonna
[70]
enter a task ID a task description a
[76]
task duration start date and an end date
[86]
for this example I'm going to use a
[89]
total of 10 tasks and let's use some
[96]
borders here
[102]
now I can start entering the tasks for
[105]
the project
[106]
so a first task is going to be
[108]
demolition which is going to take four
[113]
days to complete the next task is very
[116]
install new tiles which is going to take
[120]
two days
[127]
and flying now delivering the customer
[132]
it's gonna take a day the start date for
[138]
my first task is gonna be made first
[140]
like we said before now the end date
[144]
should be equals to the start date plus
[148]
the duration of the task in this case
[151]
the Malaysian is going to last for days
[154]
therefore the end date is gonna be May
[156]
5th for this project I'm going to assume
[160]
that I can only start a new task a day
[163]
after I finish the previous task that
[166]
being said the start date for new tiles
[169]
should be equals the end date for
[172]
demolition plus one day therefore May
[176]
6th to get the end date of new tiles I'm
[181]
going to use the same formula that I
[182]
used above and I'm gonna fill down by
[184]
using control D so the end date for
[188]
install new tiles its May 8th since it's
[190]
gonna take two days to complete the task
[192]
I'm going to fill down this formulas all
[197]
the way to my final task I can see here
[202]
my starter is made first and my end date
[206]
is May 28th now that I have the list of
[209]
tasks the duration of each and the start
[213]
and end date for each the next thing I
[215]
want to do is that timeline next to my
[220]
first table I'm gonna start with my
[222]
start date which is May 1st I don't
[228]
wanna come to be this wide so I'm gonna
[231]
choose a vertical alignment
[238]
ninety degrees and do some formatting
[246]
and then I'm gonna draw this column till
[251]
the end of the month
[252]
I now have May first until May 31st the
[259]
last thing I'm going to do here is the
[261]
calculations let's start with project
[263]
duration in order to calculate the
[265]
project duration I need the project
[267]
start date and the project end date I'm
[269]
gonna delete this value here my sterday
[273]
will be the minimum value in the start
[277]
date wrench enter my end date is gonna
[282]
be the maximum value in my end date
[285]
wrench enter the pre iteration is going
[289]
to be the difference of the two end date
[292]
- start date enter my project duration
[297]
is going to be 27 days next thing I
[300]
wanna do is compare each column to the
[304]
start and end date of each task I wanna
[307]
start with me first I want to compare
[311]
May first and see if it's between the
[313]
start date and the end date for that I'm
[316]
going to use the if formula and within
[319]
if I'm gonna have a logical test that
[322]
includes the end formula which has two
[325]
portions number one is May first greater
[331]
than or equals to my start date does the
[338]
first half comma and is May first less
[344]
than or equals to my end date close
[351]
parenthesis if these two are true
[355]
I want Excel to drop an X in here if
[360]
it's not true I wanted like so I'm gonna
[362]
use quotation mark edition mark close
[364]
parenthesis and enter
[366]
and yes in fact may first falls between
[370]
my start date and mandate since I'm
[373]
going to be copying this formula to the
[375]
right I wanna lock my column D and I
[380]
want to lock my column E and since I'm
[385]
gonna be dragging this formula down I
[386]
wanna lock my row 5 and my row 5 enter
[393]
when I copy this formula down and to the
[397]
right
[400]
Excel does the trick so now I have a
[404]
timeline the last thing I'd want to do
[406]
is change this XS for a nice formatting
[409]
so what I'm going to do is select this
[413]
range I'm gonna use some conditional
[415]
formatting where I'm gonna say if the
[418]
cells are equal to X I wanna have a
[426]
green fill and I'm gonna pick the same
[430]
font color so I don't see an X but
[433]
instead a green square now I have a time
[438]
line so now I can play with the date in
[440]
my project for example let's say my
[443]
tiles are delayed and instead of taking
[445]
2 days it's actually gonna take 4 days
[448]
let's see what happens to the timeline
[450]
oh the whole thing got delayed
[453]
now my prey duration is 29 days and my
[456]
parade end date is 31 days and let's say
[459]
that installing new cabinetry snuck away
[463]
three days is only gonna be one day
[465]
let's see what happens to the timeline
[467]
it'll just automatically time for the
[471]
bonus trick let's say your project is
[474]
delayed by two months so your new start
[477]
date is no longer May 1st
[479]
but July 1st let's see what happens to
[482]
the timeline
[483]
oops we lost it main reason is because
[485]
on the top we have the amount of main in
[489]
order to fix this we're going to say
[493]
this start date is always going to equal
[496]
my start date in this cell
[501]
the value in this cell is always going
[503]
to be equals my start date plus one day
[508]
and I'm going to copy this formula to
[511]
the right by filling it with ctrl R my
[518]
time line is back regardless of the
[521]
start date so let's say my new date is
[523]
actually Todd was first the dates on the
[528]
top changed as well
[530]
let's try one more time let's say my new
[534]
start date is October the first when I
[536]
hit enter the dates on the top change to
[540]
the month of October my new project
[543]
start date is October the first my new
[545]
project end date is October 28th my
[547]
project duration is 27 days and I can
[550]
continue playing with the timeline that
[551]
way I want so let's say 5 3 3 any
[558]
updates automatically that's it for
[561]
today if you have a chance that you can
[563]
help with whether it's at work at home
[565]
or at school don't forget to put in the
[567]
comments below I try to resolve the most
[569]
common challenges of our viewers and
[571]
subscribers thanks for watching and I'll
[573]
see you in our next video
Most Recent Videos:
You can go back to the homepage right here: Homepage