- Okay, if you're using Google Sheets,
and you find yourself wanting to create a Gantt chart,
so you want to keep track of projects
and you want visual representation of wherever they are,
we're going to go through how to create one right now.
And we're going to kind of talk about some of the details,
so that when you create yours,
you can customize it however you want,
because you'll kind of have a good understanding
of how it's built.
So what you're looking at right now is kind of
the finished version that we'll be getting to
as we walk through it.
And if we go to sheet two,
I kind of left it more wide open here
so we can build it out.
Alright, so what's happening is that
you can build three rows, and in these rows,
you can have numbers that represent the year,
month and the day.
So I'm doing mine in May 2020.
And you extend these out as far as you want.
So we come over to the right hand side.
And if you're not stopping at June 9th, 2020,
add as much as you want to the right,
you can, say you want to add five days,
or however many days I'm highlighting,
if you right click and insert those to the right,
then you don't have to do one at a time.
But anyway, so add as many columns as you want.
But do it in this order, year, month, day,
and then we're going to circle back
to using those in a second.
And then over on the left hand side
are some of these that are,
kind of more user inputs that are going to be
specific to your sheet.
So when you build yours,
this will probably be very similar.
And then you would come on in the column A
and then you would describe your projects.
And then in B and C, you would put in the start date,
and the end date.
And the only thing you have to be kinda careful about here
is just make sure you have a valid date.
So, 5/5/2020 here is written in a way
that Google Sheets recognizes that it's a date,
if you just kind of type it out in plain English
have an extra space or say, May 5th and put a th in it.
It's not going to work, right
because it needs to be able to recognize it as a date.
So if I double click in that,
that's one way to check, little pop up calendar comes up
so it knows that's the date.
And now what we're going to do is build out
Conditional Formatting in this area
that gives us the visual that you wanna see
to track your projects.
So let's just select the whole area right now
that we want to format.
So we'll go over to the right,
I've zoomed in a little bit
so it's a little bit easier to see.
So I had to scroll to the right.
Regardless, I have that area highlighted.
And I've already done this.
That's why you see the menu to the right
but go to format, conditional formatting.
And when you left click that you get this menu on the right
and it says add another rule.
So I'm going to left click to add another rule,
leave it on the single color for now,
this will all be done in single color.
And because we selected the range in advance,
this is already filled out.
So that's right, don't worry about that.
And now we'll come down and this is where the magic happens.
That's all, it's preset to kind of a simple formula here.
Let's left click on that you get a drop down
and come down to the bottom
where all the flexibility is,
and you can just input your custom formula.
Left click on that,
and you just get this open box
that's waiting for a formula and type it into here.
But first, we're going to type it into a cell
because the drawback of just typing a function into that box
is that you don't get the little box of helper text
that comes up and gives you some hints about what to do.
So it's easier if you want to experiment,
to just put it in the cell first.
So what we're going to do
is we're going to look at
the date that's being referenced right now.
So the date in column D,
and we're going to see if that's greater than five.
So let's talk about how we do that.
And what greater than five means.
There's a function called Weekday.
And what Weekday does is it looks at a date,
and it tells you what day of the week it is.
So we're going to use it where Monday is a one,
Tuesday is a two, and all the way through Sunday,
that'll be a seven.
Here's one of the tricks is that
we need to use the date function inside of Weekday
to create what date we're looking at here.
So let's type the date
and then use the parentheses again.
So once you do the second parentheses,
it tells the box to start giving you hints
about the date function.
And it takes three arguments.
So once the year, the month and the day,
and then once you give it those three,
it's going to make those into a valid date
that you can use inside of a formula.
So the year in this case is in D3, right, so D3.
And then the month is D4, how convenient,
we already put these in here.
Imagine that day is D5.
So we're going that, date function is done,
close that off with another parentheses,
closing parentheses.
And now we're back to the weekday function.
This is why we do this outside of the custom formula box,
because you get these nice hints.
So we're back at the date function, we gave it a date.
Now we want to designate a type.
So here, we're just going to use a two
and what a two does,
is that it tells Weekday that that Monday is a one.
If you don't do that Sunday as a one
and just makes the function trickier.
We don't like tricky.
So let's do Weekday
two, and we're just going to hit enter now
to see what's happening.
So let's drag this out.
This is a five, five one must have been a Friday.
And let's check that so let's go down
to the little calendar here, what was five one?
Five one was Friday.
So this is returning the day of the week.
And we're going to use that result
to format whether or not this is weekend.
So let's grab this function, I'd say we're ready to use it.
And you all have to forgive this green here,
some conditional formatting is already applied.
Because I have half of a formula over here.
It's jumping the gun, we'll get rid of that.
Paste that function in.
So this conditional formatting rule is not quite done yet.
All it's doing is looking to see if,
what the weekday number is,
we want to give a true or false
that's how Conditional Formatting
really should be evaluating things.
So you wanna see if it's greater than five,
because if it's greater than five,
that means it's a Saturday or Sunday.
So we have our formula mostly done.
But if you look at it,
it's only working right now on row eight,
because the conditional formatting so
when it's moving to cell E8 and evaluating,
it's working right because it's looking at column E.
But when it shifts down to row nine,
the row numbers shift in this formula.
So instead of looking at D3 for the year,
it starts looking at D4.
So you have to fix the row numbers.
And you oftentimes have to fix the row or the column
when you're using Conditional Formatting
and arrange and, it just takes trial and error
because it can be a little bit tricky.
But we have it done, this is working right,
so we've fixed the rows correctly,
did not fix the columns,
and everything greater than five has been highlighted.
So let's just do one last thing.
Let's just change that to kind of a more subtle,
let's do a gray, maybe it works.
Alright, so we have the day the week formatting done.
Let's click done and you'll see
wherever you click in this range, it's showing that rule.
Now the other one that we wanna do
is kind of the whole heart of a Gantt chart
is where it highlights the days
where each project begins and ends.
So let's reselect this area.
Go back to kind of our starting point here,
so we can see the one Conditional Formatting rule.
Let's left click on add another rule.
And let me get these numbers out of here
just because they're a little bit distracting.
I got rid of my range, hold on.
Okay, now what we're trying to do is highlight these cells
if they're within this range of dates, right,
so let's, just to talk that through in plain English
before we type in the function,
we want to highlight the cell if the value
of these dates on the top is equal to
or greater than B8, right
and less than or equal to C8,
so in this case, you wanna start the function with an and.
And that means there's two arguments here
that you need to satisfy both of them.
Needs to be greater than or equal to,
and it needs to be less than or equal to those two dates.
Otherwise, don't format it.
And to get this formula to work, right,
I'm going to cheat
and go to where I have it working already.
I won't put you through the pain of me working through this,
I'll grab the function,
we'll come over to the sheet we're working on.
And I'll paste it right down here.
We're going to take a look at this.
So we started with the and like we talked about,
so that's saying both of these need to evaluate as true.
The first, this looks at B8
and make sure it's less than or equal to
the date in column D,
and then it looks at C8
and make sure that's greater than
or equal to the date in column D.
So take a second look at that formula.
I'm going to grab the whole thing.
And I'll paste it into this Conditional Formatting.
Right here.
You can already see that it's working, I'll click Done.
Let's just delete this little guy down here.
And there you go.
So now you have a working Gantt chart
where you can just change the dates to
whatever time period you're working on,
you can expand it out to the right as far as you want to go.
This next video that we're gonna show
is how to create an interactive checklist,
where when you're done with something,
you click the checkbox and it strikes through the task
and makes it a gray background.
It's pretty cool.
I'll see you in the next video, thanks.