Gant chart / Conditional Formating
Posted by kevin on January 21, 2002 2:00 AM
I read an article where conditional formating was used to create a Gant chart. I wish to do something similar but using time instead of date.
I have a sheet where I list vehicle registrations down the left with Start Time and Finish time. The headers across the top are hourly segments starting at 00:01 through to 23:29.
However the formating does not appear to work if a start is say 22:00 through to 07:00.
Additionally I would like to include other critera such as break time which would again be a start, end time.
Can any one help please
Regards
Kevin
Posted by Stijn Vanwinckel on January 23, 2002 4:38 AM
Hi Kevin,
If you want to color the hours when the start time can be greater then the stop time (from 10 pm to 5 am) you can use the following formula:
=IF($B5<$C5,IF(J$1>$B5,IF(J$1<$C5,TRUE,FALSE),FALSE),IF(J$1<$C5,TRUE,IF(J$1>$B5,TRUE,FALSE)))
This will check to see if column B (the start time) is less then the value in column C (the end time). If so, we are going to test to see if the value in column B is greater then the value in row 1. In that case we also have to check if the value in column C is less then the value in row 1. If both cases are true, we return TRUE (apply conditional formatting). If either one is not true we return FALSE (don't apply conditional formatting).
If the value in column B (the start time) is greater then the value in column C (the end time), the test is to see if the value in row 1 is less then the value in column C. In that case, return TRUE. If the value in column 1 is greater then the value in column C, check to see if the value of column 1 is also greater then the value in column B. If this is the case, return TRUE. If not, return FALSE.
Kindest regards,
Stijn Vanwinckel
Posted by kevin on January 23, 2002 4:54 AM
Hi Stijn
Fantastic it works a treat one further question if you can assist.
Is it possible to include a second set of criteria ie the break time start and finish which would colour the respective cells a different colour.
I know its a lot to ask but its been bugging me for quite some time
many thanks Kevin
Posted by Stijn Vanwinckel on January 23, 2002 7:00 AM
Hi Kevin,
You can't put the break times in another color, because you can only have three conditional formatting conditions. So you can't put the break times in other colors. What you can do is put the break times without conditional formatting, so it shows up in the same color as the background. I will mail you the formulas for that later, with an Excel Workbook example.
Kindest regards,
Stijn Vanwinckel
