Using conditional formatting to create an hourly Gantt chart in Excel.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1799: Gantt Chart by Hour.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. I'm on Twitter the other day and saw this note, have used your YouTube “how to create a Gantt chart in Excel”.
How do you change the scale to hours? I've tried everything.
I'm not even sure they were talking about my video. But I knew that I had a Gantt chart video out in YouTube about using conditional formatting. But that was where we had dates going across the top top a start date and an end date. Now we have hours and so we have hours across the top here I want to format this CTRL+1 on the number tab I'll go with custom and just get rid of the minutes to try and make it a little bit smaller 6 am.
I'll say the alignment we will make that go vertical like that click OK and we can have the columns be a little bit less wide so they all fit on the screen. Conditional formatting we're going to use a formula in conditional formatting it's easier to build that formula right here in the spreadsheet. The formula is =AND and to conditions have to be true the start time of the project so click on B2 I'm going to press F4 one two three times to lock that down to column B so is the start time <= to this hour and click up here I'll press F4 one two times to lock it down to just row 1. That's our first test the other test is to see if the end time C2 press F4 three times there is > or = to. I really need like 6:59. But, what I'm going to do is use the cell to the right of us and press F4 twice to lock it down to the dollar sign. One row, one and what we should get is a bunch of trues and falses and the trues indicate that the event is happening that hour so the staff meeting goes from 8 to 9 we have a true there at 8 everything else is false power excel class from 1 to 6 trues and one two three four five and falses everywhere else. Once we have that working, we're going to copy the formula for the first cell from the formula bar CTRL+C to copy it to the clipboard and then ALT+O+D for conditional formatting, new rule, use a formula and paste that formula in. We will use a fill maybe like a nice green or something like that click OK, click OK, click OK and now at this point we can get rid of the formulas themselves we don't need the false as and trues there we can actually just click the delete key and for my column width go really narrow like three and we have our Gantt chart working in Excel provided that there are no partial events. What if a project started at nine and went to 11:30 or 11:40 or something like that? Do we want to highlight the next block if it's more than 29 minutes more than half an hour? So, that dramatically complicates things. I created a couple of helper rows here to take these times and convert them to minutes of the day.
So, in a day there's 24 hours x 60 = 1440.
So I took that 6 am x 1440 to give me the Start minute and then the End minute is 59 + that number same thing over here we'll take the start time multiply it by 1440 and the end time and x 1440 and then try and build some sort of a formula here to figure out if the intersection of those two is greater than 29 minutes.
Now my first attempt at this was a formula that was based on my Kervins book where we take the indirect of the start time and the end time to an intersection of the indirect of these two times. So, the these two times has an indirect intersect with these two times as an indirect is going to try and find the rows that they have in common and see if there's more than 29 Rows. I tried to send that through to account blank. But, to my chagrin is not just counting once per row, it's counting all 16384 cells in the row and so while it would have been possible to check to see if this result was more than 475,136 which is 29 Rows x 16,384 cells. I realize, whoa wait a second this is just me way too slow there has to be a better way to build this formula.
So, I abandoned this method and started to come up with a simpler method and really it's interesting how much this got simplified if I take the minimum value of the two yellow cells the endings time here in the ending time here and then subtract the maximum value of the two starting cells. All right, so the minimum value of these two is minute 419, the maximum time of these two is minute 480 take the 419 - 480 and see if that's greater than 29 was my goal and you can see here most of them out here are negative, but we have a few that seem to seem to match even with this 930 here we're getting the eight o'clock time and the nine o'clock time because there's 30 minutes. All right, so that formula =min of the two yellow cells - the max of the two orange cells we want to do that compared to 29 so we just simply add a > 29 again copy this whole formula from the clipboard CTRL+C and then all oh D I've already set up this formula so I'll just edit the rule paste that in there change it to green and click OK and it will work we can get rid of those formulas now. It's ugly to have these two helper rows and helper column so we could just simply format row hide and format column hides, but they're still there and still ugly if you wanted to you could take that formula. I have in the last one and change it to this formula which does not use the two helper rows or helper columns it's actually taking see 2 x 1440 the formula gets a little bit longer, but of course it would work out O D at the rule paste it all in there and then we don't actually need to show the helper rows or columns.
Finally end up with this example here. So, let's just do a little test. Let's say that the project B goes from 9:00 a.m. to 10:15 so that is only one block because we're not using more than half at the 10:30 I'll change this to 10:31 and then it highlights that second block so interesting. If you need to break it down by partial hours and then you're stuck with this longer formula but of course we could always just go back and use the unformat test for two conditions if we're looking for entire hours.
Alright, so there you go a couple of different ways to do a Gantt Chart by Hour. Want to thank you for stopping by, we'll see you next time for another Netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1799: Gantt Chart by Hour.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. I'm on Twitter the other day and saw this note, have used your YouTube “how to create a Gantt chart in Excel”.
How do you change the scale to hours? I've tried everything.
I'm not even sure they were talking about my video. But I knew that I had a Gantt chart video out in YouTube about using conditional formatting. But that was where we had dates going across the top top a start date and an end date. Now we have hours and so we have hours across the top here I want to format this CTRL+1 on the number tab I'll go with custom and just get rid of the minutes to try and make it a little bit smaller 6 am.
I'll say the alignment we will make that go vertical like that click OK and we can have the columns be a little bit less wide so they all fit on the screen. Conditional formatting we're going to use a formula in conditional formatting it's easier to build that formula right here in the spreadsheet. The formula is =AND and to conditions have to be true the start time of the project so click on B2 I'm going to press F4 one two three times to lock that down to column B so is the start time <= to this hour and click up here I'll press F4 one two times to lock it down to just row 1. That's our first test the other test is to see if the end time C2 press F4 three times there is > or = to. I really need like 6:59. But, what I'm going to do is use the cell to the right of us and press F4 twice to lock it down to the dollar sign. One row, one and what we should get is a bunch of trues and falses and the trues indicate that the event is happening that hour so the staff meeting goes from 8 to 9 we have a true there at 8 everything else is false power excel class from 1 to 6 trues and one two three four five and falses everywhere else. Once we have that working, we're going to copy the formula for the first cell from the formula bar CTRL+C to copy it to the clipboard and then ALT+O+D for conditional formatting, new rule, use a formula and paste that formula in. We will use a fill maybe like a nice green or something like that click OK, click OK, click OK and now at this point we can get rid of the formulas themselves we don't need the false as and trues there we can actually just click the delete key and for my column width go really narrow like three and we have our Gantt chart working in Excel provided that there are no partial events. What if a project started at nine and went to 11:30 or 11:40 or something like that? Do we want to highlight the next block if it's more than 29 minutes more than half an hour? So, that dramatically complicates things. I created a couple of helper rows here to take these times and convert them to minutes of the day.
So, in a day there's 24 hours x 60 = 1440.
So I took that 6 am x 1440 to give me the Start minute and then the End minute is 59 + that number same thing over here we'll take the start time multiply it by 1440 and the end time and x 1440 and then try and build some sort of a formula here to figure out if the intersection of those two is greater than 29 minutes.
Now my first attempt at this was a formula that was based on my Kervins book where we take the indirect of the start time and the end time to an intersection of the indirect of these two times. So, the these two times has an indirect intersect with these two times as an indirect is going to try and find the rows that they have in common and see if there's more than 29 Rows. I tried to send that through to account blank. But, to my chagrin is not just counting once per row, it's counting all 16384 cells in the row and so while it would have been possible to check to see if this result was more than 475,136 which is 29 Rows x 16,384 cells. I realize, whoa wait a second this is just me way too slow there has to be a better way to build this formula.
So, I abandoned this method and started to come up with a simpler method and really it's interesting how much this got simplified if I take the minimum value of the two yellow cells the endings time here in the ending time here and then subtract the maximum value of the two starting cells. All right, so the minimum value of these two is minute 419, the maximum time of these two is minute 480 take the 419 - 480 and see if that's greater than 29 was my goal and you can see here most of them out here are negative, but we have a few that seem to seem to match even with this 930 here we're getting the eight o'clock time and the nine o'clock time because there's 30 minutes. All right, so that formula =min of the two yellow cells - the max of the two orange cells we want to do that compared to 29 so we just simply add a > 29 again copy this whole formula from the clipboard CTRL+C and then all oh D I've already set up this formula so I'll just edit the rule paste that in there change it to green and click OK and it will work we can get rid of those formulas now. It's ugly to have these two helper rows and helper column so we could just simply format row hide and format column hides, but they're still there and still ugly if you wanted to you could take that formula. I have in the last one and change it to this formula which does not use the two helper rows or helper columns it's actually taking see 2 x 1440 the formula gets a little bit longer, but of course it would work out O D at the rule paste it all in there and then we don't actually need to show the helper rows or columns.
Finally end up with this example here. So, let's just do a little test. Let's say that the project B goes from 9:00 a.m. to 10:15 so that is only one block because we're not using more than half at the 10:30 I'll change this to 10:31 and then it highlights that second block so interesting. If you need to break it down by partial hours and then you're stuck with this longer formula but of course we could always just go back and use the unformat test for two conditions if we're looking for entire hours.
Alright, so there you go a couple of different ways to do a Gantt Chart by Hour. Want to thank you for stopping by, we'll see you next time for another Netcast from MrExcel.
