Cheers for the Video clip! Excuse me for the intrusion, I would appreciate your opinion. Have you thought about - Weydaniel Organized Dominator (Sure I saw it on Google)? It is a good one off product for getting 7000 plus project management and business templates minus the hard work. Ive heard some extraordinary things about it and my old buddy Taylor got cool results with it.
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 ~
Cheers for the Video clip! Excuse me for the intrusion, I would appreciate your opinion. Have you thought about - Weydaniel Organized Dominator (Sure I saw it on Google)? It is a good one off product for getting 7000 plus project management and business templates minus the hard work. Ive heard some extraordinary things about it and my old buddy Taylor got cool results with it.
@ExcelIsFun Thanks for replying -- you're so reliable after all this time. I did watch #501 first, hope I didn't miss anything. If I start a new worksheet (Ctrl-N) and type 10:58 PM in B1 (it displays 10:58:00 PM when I click there), =B1=NOW() returns false. I think it's the seconds, but I can't get rid of them. I'm tracking in 15 minute blocks, so I am going to round NOW() to the nearest quarter hour. I still can't use the equality sign, but I can use > and < in my rule and get close.
I Googled time functions for 45 minutes and none of them explained that times are in fractions of a day. That's so essential to doing the math! You helped me figure out that I can extract the time element from NOW() by subtracting TODAY() (why not just TIMEVALUE(NOW(), Microsoft?). But, I still can't compare "4:00 AM" to NOW() to see if they're equal -- even if I truncate them to three decimals. What would you do, Excel magic guy?
This video has most of what I know about time: Excel Magic Trick 501: Excel Time Format & Calculations (10 Examples) What is giving you "4:00 AM"? Are you typing it in, is it be produced by a formula? If it is text "4:00 AM" that would be different than 4:00 AM. If 4:00 AM in in a cell, say B1, then this formula would tell you if 4:00 AM = NOW(): =B1=NOW() It would give you TRUE if it was 4:00 AM and FALSE if it was not.
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.
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?????
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! Please, how can i put one break hour? For exemple: 12:00 till 13:00 = lunch. One more question is: In one day one task start on 3PM and have 4 hours duration, how can i put the 2 hours leftover on the next day? Thx in advanced!
You would have to modify the setup. See this video for a night shift time formula that may help: Excel Magic Trick 286: MOD function & Time Calculations (Time For Night Shift, or Negative Time)
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
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?
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.
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?
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.
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
Hi! You're great! But I still have a doubt: i'm creating one but instead of hours I'm dealing with days. I have people coming and going, in and out of the country every month. so, I have dates (days) of arrival and days of depart and also I must have the total days they stayed in the country. They come and go several days in one month. I could you take this into the gantt chart? Many thanks in advance!! :)
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.
Thanks for sharing. I came across a problem with the conditional formatting when I did it in 30 min increments. X axis contains time by half hour increments. I used the formula you gave for the conditional formatting but had some problems with the end time when it went to 9:30 pm. The formatting only went to 9:00 pm. Have you seen this error before? However, when I had it go to 5:30 pm, it worked. Please help.
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.
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
Cheers for the Video clip! Excuse me for the intrusion, I would appreciate your opinion. Have you thought about - Weydaniel Organized Dominator (Sure I saw it on Google)? It is a good one off product for getting 7000 plus project management and business templates minus the hard work. Ive heard some extraordinary things about it and my old buddy Taylor got cool results with it.
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.
from last post: so, I have dates (days) of arrival and days of depart and also I must have the total days they stayed in the country. My doubt: A guy that was already in the country, since the previous month (since 15 dec 2010); and he leaves on the 2nd jan. Then comes back on the 9th jan; then he leaves again on the 15th; then returns again on the 22nd; and leaves once more on the 28th. How to create a chart (Gantt) that takes into consideration so many start and end dates in the same month?
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!!!
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)
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?
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.
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
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!
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.
@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 :)
I am glad that you liked the video! Hello!
Your tricks are my favorite and I use them very often preparing for my reports. Thanks for the great videos.
Cheers for the Video clip! Excuse me for the intrusion, I would appreciate your opinion. Have you thought about - Weydaniel Organized Dominator (Sure I saw it on Google)? It is a good one off product for getting 7000 plus project management and business templates minus the hard work. Ive heard some extraordinary things about it and my old buddy Taylor got cool results with it.
I respect you teacher that's why I subscribe your channel with my different accounts.
Thank you for that respect and support!!! It helps me to keep making videos, Nahim, when you comment, thumbs up and sub : ) : )
: ) :)
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 ~
Great video! I was looking for this but then in minutes instead of hours. Simple solution: START + DURATION / 1440 (24*60minutes in a day)
Thank you so much. You legend x
I am glad that it was helpful!
Cheers for the Video clip! Excuse me for the intrusion, I would appreciate your opinion. Have you thought about - Weydaniel Organized Dominator (Sure I saw it on Google)? It is a good one off product for getting 7000 plus project management and business templates minus the hard work. Ive heard some extraordinary things about it and my old buddy Taylor got cool results with it.
Can you try where you cross the day, like 11pm to 1am if the condition will perform. Thank you.
You are welcome!
@ExcelIsFun Thanks for replying -- you're so reliable after all this time. I did watch #501 first, hope I didn't miss anything.
If I start a new worksheet (Ctrl-N) and type 10:58 PM in B1 (it displays 10:58:00 PM when I click there), =B1=NOW() returns false. I think it's the seconds, but I can't get rid of them.
I'm tracking in 15 minute blocks, so I am going to round NOW() to the nearest quarter hour. I still can't use the equality sign, but I can use > and < in my rule and get close.
I Googled time functions for 45 minutes and none of them explained that times are in fractions of a day. That's so essential to doing the math! You helped me figure out that I can extract the time element from NOW() by subtracting TODAY() (why not just TIMEVALUE(NOW(), Microsoft?).
But, I still can't compare "4:00 AM" to NOW() to see if they're equal -- even if I truncate them to three decimals. What would you do, Excel magic guy?
Neat trick with conditional formatting rather than having to create a chart from the data.
This video has most of what I know about time:
Excel Magic Trick 501: Excel Time Format & Calculations (10 Examples)
What is giving you "4:00 AM"? Are you typing it in, is it be produced by a formula? If it is text "4:00 AM" that would be different than 4:00 AM.
If 4:00 AM in in a cell, say B1, then this formula would tell you if 4:00 AM = NOW():
=B1=NOW()
It would give you TRUE if it was 4:00 AM and FALSE if it was not.
Hi. What if I wanted to see the highlight bar from 8am including 10am, what then is the formula?
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.
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?????
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
Do you have the conditional formatting formula to highlight hours at a specific date?
Nice chart, good work!
Great video! Please, how can i put one break hour? For exemple: 12:00 till 13:00 = lunch.
One more question is: In one day one task start on 3PM and have 4 hours duration, how can i put the 2 hours leftover on the next day?
Thx in advanced!
Did you ever get this figured out? I'm having the same problem with the last hour blocks are always red. Thanks.
You would have to modify the setup. See this video for a night shift time formula that may help:
Excel Magic Trick 286: MOD function & Time Calculations (Time For Night Shift, or Negative Time)
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
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?
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.
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?
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.
nice video sir! i am currently working on a project which requires similar formulas. thanks for the video
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
is Gantt Chart by Hour also possible with google sheet?
Hi!
You're great! But I still have a doubt:
i'm creating one but instead of hours I'm dealing with days.
I have people coming and going, in and out of the country every month.
so, I have dates (days) of arrival and days of depart and also I must have the total days they stayed in the country. They come and go several days in one month.
I could you take this into the gantt chart?
Many thanks in advance!! :)
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.
Thanks for sharing. I came across a problem with the conditional formatting when I did it in 30 min increments. X axis contains time by half hour increments. I used the formula you gave for the conditional formatting but had some problems with the end time when it went to 9:30 pm. The formatting only went to 9:00 pm. Have you seen this error before? However, when I had it go to 5:30 pm, it worked. Please help.
I am having the same issue, did you ever find a resolution?
I'm having some weird issue also. I used half hours from 00:00 to 23:30 and the column 23:30 is all highlighted. I need help.
I have a different problem. I need a chart exactly like this but most of the continue on into the next day. Any differences??
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.
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
Cheers for the Video clip! Excuse me for the intrusion, I would appreciate your opinion. Have you thought about - Weydaniel Organized Dominator (Sure I saw it on Google)? It is a good one off product for getting 7000 plus project management and business templates minus the hard work. Ive heard some extraordinary things about it and my old buddy Taylor got cool results with it.
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.
from last post:
so, I have dates (days) of arrival and days of depart and also I must have the total days they stayed in the country.
My doubt:
A guy that was already in the country, since the previous month (since 15 dec 2010); and he leaves on the 2nd jan. Then comes back on the 9th jan; then he leaves again on the 15th; then returns again on the 22nd; and leaves once more on the 28th.
How to create a chart (Gantt) that takes into consideration so many start and end dates in the same month?
I have a query......
Instead of conditional formatting I want Minutes to be plotted in time intervals
how do you create a Gantt Chart instead of time but by months? do you have video for this?
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!
you are so smart!
nice i very well said! oh i just clicked subscribe! this is so cool
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!!!
Is it also possible to make a gantt chart with minutes instead of hours
yes
i love it!!!! thanks
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)
+Zachary Ogborn
Not as cool lol
How can I do in military time from 0400 till 0000 (midnight)?
Hi, amberjudd1.
Yes, I did. If you want I can send you the workbook with it.
Awesome !!!
I do not understand what you are asking.
For what?
awesome.. Thanks a ton
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?
Did you get the formula for different colour for different tasks?? Even i need it.
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
did you get a solution for rolling hours into next day?
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.
how do i download the software
Auto Correct maybe?
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
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!
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.
@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 :)
Can you send me that form via email?