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

[Duschmon7]

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]

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

[ges05]

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?

[ExcelIsFun]

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!

[ges05]

@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 :)

[NationalMemGuest2009]

Great video. I have experimented with your model, but I have a situation where I have several people doing multiple projects that start and stop during the day. Can this be modified to show multiple projects per person along the horizontal? For example "A" has three projects, "B" has two projects, etc. I did a search through your site, but did not see this. Thanks for the education!

[NationalMemGuest2009]

Great video. I have experimented with your model, but I have a situation where I have several people doing multiple projects that start and stop during the day. Can this be modified to show multiple projects per person along the horizontal? For example "A" has three projects, "B" has two projects, etc. I did a search through your site, but did not see this. Thanks for the education!

[ges05]

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!! :)

[mdgrubio]

Good Video...

can you send me a link to direct download about conditional format sir, i will used it to my schedule.. much thanks

[aswilldf]

i love it!!!! thanks

[ExcelIsFun]

You are welcome!

[Noumenon72]

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?

[ExcelIsFun]

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.

[Noumenon72]

@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.

[ExcelIsFun]

Try this video for round to nearest 5 minutes (then you do 15 minutes):

Excel Magic Trick #238: Round Times To 5 Minutes

Something like this will work:

=ROUND(NOW()/"00:15",0)*"00:15­"

or

=MROUND(NOW(),"00:15")

[LeBaron74]

Nice chart, good work!

[ExcelIsFun]

I am glad that it was helpful!

[waseemakhlaq123]

when i enter something in excel sell they give me suggestion like data base dropdown wise can u tell me 

[ExcelIsFun]

I do not understand what you are asking.

[krn14242]

Auto Correct maybe?

[ExcelIsFun]

For what?

[sirflamo79]

Will this work past 12am?

[ExcelIsFun]

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)