Excel Magic Trick 626: Time Gantt Chart -- Conditional Formatting & Data Validation Custom Formulas

Do you have the conditional formatting formula to highlight hours at a specific date?

is Gantt Chart by Hour also possible with google sheet?

Can you try where you cross the day, like 11pm to 1am if the condition will perform. Thank you.

Thank you sir for nice and clear video, I have a question pleas. I make a table that contains in culomn A doctors names and the headers in row1 is the month date. Inside the table all the cells has data validation with departments in hospital to make a monthly rota shift for the doctors. The list of the data valedation contains (main department, long shift, short shift, emergency, operation room, DAY OFF).
I want to make a conditional FORMATING THAT IF I CHOOSE "long shift" from the data validation automatically gives this doctor "DAY OFF" for the next day and color the cell of "DAY OFF" in red.
How can I do that.( For example if I choose in C5 "long shift" automatically mak D5 "DAY OFF" with red background in B5.) thank you.

How would you do that if it’s an hourly schedule (15 min int) crossing several different dates? I can’t figure out how to include the dates in the calculations

Is there a way to add breaks and or lunches? Also is there a way to sum the total colored cells each hour? It would be very helpful to see at a glance how many people were staffed each hour

Great video thanks. I have a question however; in your time gantt chart you have 2 projects being worked on at the same time, which strikes me as not possible. Is there another logical test so that the times don't overlap? Thanks

Great video! I was looking for this but then in minutes instead of hours. Simple solution: START + DURATION / 1440 (24*60minutes in a day)

How can I do in military time from 0400 till 0000 (midnight)?

I respect you teacher that's why I subscribe your channel with my different accounts.

Thank you very much for this video. This awesome! I have a quick question though. I don't understand why the colored line for the timing does not start from the exact Start time when starting with PM. Thank you for your help.

Nice but never found formula for a job that occurs in time intervals? these only highlight once in a row, but if breaks or repeating more than once a day there is no conditional formatting guide nor tutorial. Is there any way?

Can you send me that form via email?

This is awesome!! Almost exactly what I need for my little project. In addition to what you show here, I need to fill a different color for any of those with overlapping times. Mine is for job schedules for application/database maintenance. For example, 12pm through 4pm all have some that overlap, and I would want those to be a different color.

Hi, if column C contains data for x and column d data for y, and is already conditionally formatted and a and b contains date and time is it possible to get the output of formatted data of start date to end date of c and d column.

Hey great video please tell me if I have input condional format in columns and in rows there is time and date is it possible the output to be like from this date to that date the data which is condional formatted.

I use the Gantt project planner template and I would like to use 3 different colours for each task in my project. How can I combine formula = plan with conditional formatting that contains the name of my task?

nice video sir! i am currently working on a project which requires similar formulas. thanks for the video

I have downloaded the file hoping I could expand the hours range. I constantly use/need a 12 hour schedule range. I have tried to change the file validation with no luck. Could you help with this? If you reply I could send my e:mail address or we could communicate directly.. Thank you.

This was fantastic, thank you so much for publishing this.

I have been struggling with Excel Gantt charts for ages, it has been literally haunting my dreams!

I don't suppose you could advise how to alter the shading to represent a percentage column? Or perhaps you already have a trick video and I've missed it. Thanks.

Is it also possible to make a gantt chart with minutes instead of hours

i am watching your videos recently and find those very interesting.

i am planning to make a gantt chart with the below criteria:

1. there would be start date, target date, and date submitted (i prefer using stacked bar)

2. colors are: green for remaining days to complete; red for start date up to today's date; blue for date submitted

do you have similar videos on this?

appreciate your response.

more powers!

Hi there,

I need your help with conditional formatting please?

I've a project timeline on a week and month calendar form. I've applied conditional formatting on a row range (blank cells on the calendar), based on inputting of a start and and date of a given task.
I also need to apply conditional formatting on those same row range (blank cells on the calendar), when selecting a certain resource drop down list. So to summarise:

I need conditional formatting applied on a row range based on dates.
I need conditional formatting applied on the same row range based on a criteria (text).

How do I do that?

Thanks a million!!!

Hi Sir,

Thank you for that wonderful presentation. I would like to ask if you could help me with my problem. I am currently creating a progress monitoring report and I have data which looks like this:

On sheet 1 Column A, let's say I have the name of my staff

AAA
AVM
RGR
AVM
AAA
AAA
RGR

On column B, their submitted reports like this

Report 1
Report 2
Report 3
Report 4
Report 5
etc.

On sheet 2, I have a dropdown cell for Staff and a dropdown cell for Report.

What want to do is when, for example I chose Staff AAA, I will only see all the Reports of staff AAA to the Report Dropdown.

Thank you for your time

awesome.. Thanks a ton

Instead of using conditional formatting to hide the cells that aren't needed wouldn't it be easier just to right the formula this way in the C column?   =IF(OR(A2="",B2=""),"",A2+B2/24)

you are so smart!

Awesome !!!

how do i download the software

Hey great work! :) Loving your videos!!

I have a quick question I'm trying to do this however I need the highlighted cells going down a column instead across. Would I still use the same formula and adjust this some how? Could you kindly point me in the right direction please.

Many thanks in advanced

Neat trick with conditional formatting rather than having to create a chart from the data.

I have a query......
Instead of conditional formatting I want Minutes to be plotted in time intervals

This works great, except for a couple of glitches.  I followed the instructions to create a 24 hr work schedule for start and end times. The formatting automatically keeps 2300 (11 pm) and the last column hour (0500) shaded for every employee, regardless of their start time. What is wrong with my formula and how do I fix it?

did you get a solution for rolling hours into next day?

Your tricks are my favorite and I use them very often preparing for my reports. Thanks for the great videos.

nice i very well said! oh i just clicked subscribe! this is so cool

I am glad that you liked the video! Hello!

how do you create a Gantt Chart instead of time but by months? do you have video for this?

Hi, Thanx so much for your detailed video!! I have a basic knowledge of excel and have been trying to find uncomplicated instructions on entering formulas for weeks. After working along with this video, with no problems, I now understand how to make my gantt charts display the info I want. Although I didn't use all the techniques described, I was able to create a weekly chart spanning 14 months that can be easily updated by anyone. Thanx again! ~ D ~

Love this channel!!!! I have a wierd problem after using the true/false formula that shades the cells in the Gantt Chart. I set up my spreadsheet for 30 minute time intervals instead of hour intervals. If, for example, I have a start shift of 8am-- the cell that is shaded is not 8AM but 8:30AM. This runs true for every start shift--the cell 30 minutes after the start shift is shaded. The cell formula is 100% correct. ANY IDEAS?????

Yes, please! [email protected]

Hi, amberjudd1. Yes, I did. If you want I can send you the workbook with it.

Did you ever get this figured out? I'm having the same problem with the last hour blocks are always red. Thanks.

Hello, I need a bit of help with my Hourly Gant Chart. I have mine set up similar to yours but I wanted to add more to it. I wanted 2 separate colours used. I want the entire shift of an employee highlighted in green, however, I want to add a red shade to it so that when I enter their lunch break, the green shading for the given timezone will change red. Is this possible? If so, please explain how so I can change my graph. Thank you.

Yes, there would be a difference. I do not have a video for thet. Try asking a detailed question at this site: mrexcel[dot]com/forum

I have a different problem. I need a chart exactly like this but most of the continue on into the next day. Any differences??

@ExcelIsFun Hi. Thank you very much. I manage to do it, but I'm having a problem with the last day. Always red. No matter if it has data or not. I'll post it in mrexcel[dot]com. Many thanks :)

Totally possible. But I have no time. 80 = hours a week at work right now. Try THE best Excel question site: mrexcel[dot]com Post a clear question and you will get help!