Create a Gantt Chart for Hours in a Day

[@DougHExcel]

See my other Gantt Chart Videos at kzbin.info/aero/PL-n8f1cY_Qw_45tC8YR0M1HXrZZ2x3DEb

[@DougHExcel]

...

[@firstmighk]

Thanks Doug! This is exactly what I've been looking for! Gantt chart templates are everywhere for "by the day", but hardly anything out there for 'by the hour'. Just created one for automation tasks that run at certain times/durations in a 24-hr period. Taking it a step further, I added a column for which day or days a certain task runs, and the highlighting color varies if a task runs on Sat, or runs on M-F, etc. I'm stuck though, on when a task starts at 23:00 and runs though 01:00. Still working on it though, and can 'phone-a-friend' if I need to. Thanks again, love your videos!

[@DougHExcel]

yeh that gets tricky with it cross over the next day. An alternative approach is in cell gannt and then included date/time is rows heading, but that can get complicated too.... kzbin.info/www/bejne/jHeWfIKgdquMrM0

[@femalien316]

This worked perfectly in google sheets as well. Thank you so much!

[@DougHExcel]

Hi femalien 316, glad you liked! Feel free to subscribe to my channel as a there are a bunch of these videos and I have new ones published weekly.

[@morasekhi]

Thank you for this video, simple and useful. well done.

[@DougHExcel]

Thanks for the comment, glad you liked!

[@chavonnetaylor6436]

Could you advise how I could use this example, but to show time zone overlap between team members?

[@barttitulaerexcelbart9400]

Thanks Doug, you could enlarge the model by adding a date and make a table. Then filter (with slicers the date, or person or tasks). But a question: I tried to make this with a graph, works fine as well the only problem is the stetting of the axes, so it only displays quarters of an hour. Your examples works fine with whole hours, but if you have different times (like 12:25) , you need a graph, maybe something for a new video? greetings Bart

[@DougHExcel]

Hi Bart , thanks for the comment and the next potential video idea : D

[@echokane]

Greetings! This is almost exactly what I need, there are just two functions missing. How can I make this span over 24 hours with multiple dates and also track multiple times per day that are not sequential? I have scoured all over the internet to find a Gantt chart to track my sleep. I have been filling one out manually for years, but it would be really nice to be able to just input the times when I go to bed, wake up, or take naps throughout my day. Sleep tracking is becoming quite a trend! Might get a handful of views if you wanted to make a video surrounding that! Feel free to reach out to me, and I can give you the template I have been working with for inspiration! Thanks again for all your videos!!!

[@Tc558]

did you figure out how to get it to span 24 hours? it's something I'm trying to figure out as well

[@harikumarpalapparambil6653]

Can you please share a video, how to make chart similar to Gant chart with a time slot of 1 hr with a duration of an activity in power bi. Like employe login / logout ( date time column). Is there any chart which can plot hour slot duration of an activity, like a Gant chart in power bi.

[@theliquidstate3216]

very useful but has an issue after 12am. can yo do a video on that?

[@Sean.Q]

Awesome explanations and very useful.

[@DougHExcel]

Glad you think so!

[@metseia]

Thank you for the video! Could you do an update with the latest 2020 Excel version 16.41? I was following along but when it got to New Formatting Rule under Conditional Formatting, the new Excel version looks very different and I wasn't able to complete the tutorial. Thanks!

[@DougHExcel]

Hi metseia, thanks for the comment! Maybe a future video 😉

[@harshitbakshi9197]

Hello, Any solutions for the overnight time stamp. Since the formula does not takes AM after PM, can we add another condition formatting or divide the clock time after midnight or do something that can result the overnight hours?? Please assist.

[@DougHExcel]

A day is calculated based on the number behind the decimal (i.e., 0.5 is half the day at 12:00 PM) so you might want to try conditional formatting that takes that into consideration.

[@adamrosenbaum9944]

Doug, I need to create this on a 24 hr. cycle where some employees work an overnight shift. i.e. 7pm-7am. The logic in your code will not fill the correct cells. My times across the top start with 7am and end with 6am. When using the start/stop times 7pm and 3am, the only cell to be filled in is 6am. using 12am-8am, the only cells shaded are 6am and 7am. How do I adjust accordingly. I suppose a question to ask would be at what point of the day is 2am considered more than 7pm or is it less than 3am because when you work an o/n shift it can be both.

[@DougHExcel]

maybe an in cell gantt with the columns down to the hours work? kzbin.info/www/bejne/jHeWfIKgdquMrM0

[@davidallender9158]

I am running into the same issue with getting Overnight shifts and hours to populate correctly. I did not really see how it would be possible with the video that you shared with Adam. I would think that if I changed the formula to specific cells that are for the Overnight Team, it could force them to those few cells to function correctly.

[@AkaExcel]

Thank You!

[@DougHExcel]

You’re welcome!

[@jamescoble6844]

How do you do the it 30 min increments?

[@DougHExcel]

See this part of the video kzbin.info/www/bejne/fGrMeHxvgL-KhNE

[@eryndonaldson6867]

@@DougHExcel is the formula different if you 30 min increments? I tried and now, if I have a time out that ends in a 30 min increment the fill will only go to the hour before? so a 4:30 out, the fill goes thru 3:30 but not 4:00 like it should. The time in works just fine if it is a 30 increment. So strange.

[@orca708]

@@eryndonaldson6867 im experiencing the same. When the time increments by 30 minutes in the chart, the chart doesn't seem to display correctly. :( Thank you for the tutorial though. It would be awesome to see you make another one of this video with 30 minutes increment.

[@oneworldsteve8433]

this is great but can you have Name 1 come to work from 8am -10am then come back from 11 am - 2 pm I am trying to find a chart to draw up an event program where multiple events take place during same time periods and at separate times

[@tressone95]

I am in need of this as well. I tried to make a second set of time in and time out columns and format them the same but it did not work.

[@DougHExcel]

some other gantt videos may give insight at kzbin.info/www/bejne/ipvbfZKehtNorZI

[@amandapadilla4322]

I used two AND formulas inside an or formula. The first and formula was for the first start and end time, the second and formula was for start 2 and end 2.

[@patrickmcgee9910]

How can I make this work for a 24 hour clock for 24 hours a day?

[@DougHExcel]

Have you tried to change the display to show 24 hour time?

[@dontliebehonest6545]

Excel complains that my formula is incorrect and I am doing exactly what you are doing. I can't figure out what I am doing wrong

[@snagbag06]

Why is this so difficult. I just want to make a simple Gantt chart, not build a while damn website. 😒. Thanks for the video though. I was able to follow until Excel started demanding damn formulas.

[@gilbertrivera50]

Great video! I ran into a problem with my circumstances. My schedules work day runs from 7am-4am the following day, and there are several people that span over the midnight hour. Those that do break the rule since excel sees 0100 as less than 2300. Any help with fixing this issue? Thanks!

[@DougHExcel]

maybe one of this will give insight >>kzbin.info/www/bejne/ipvbfZKehtNorZI

[@ZairaGGil]

@@DougHExcel I had the same problem as Gilbert Rivera, and checked the list of your videos, but none of them solve the issue. Problem is if shift goes from 20hr to 01:00, it only highlights until 23:30. And it always highlights 23:30 even it the person is not scheduled at all... bummer! any help!? thanks for the videos by the way!!

[@dandrem.4074]

Well this definitely didn't work for me.

[@JoãoSchoeppen]

Makes no sense In my opinion to have the E1 in the formula should be both D1.

[@stephaniedsouza7770]

Pmts tree p

[@DougHExcel]

Sorry, don’t understand the comment/ question...

[@stephaniedsouza7770]

Pmts tree By p

[@DougHExcel]

Hi Stephanie D'souza.p., thanks for the comment!