Hi,
I've created a Gantt Chart to display the activities of individuals with a learning disability in a supported living house. The chart has a time range from 6am to midnight, with a column for each 15 minute increment between the two times, with 7 rows, one for each day of the week. Any activities that are input in a separate section will appear on the Gantt chart. This is done by using an IF(AND formula to see if a time falls between the start time and end time of the activity. If it does, it will appear as a 1 on the grid, otherwise a 0. Using a conditional format, this will appear as a green block. So, an activity between 08:00-09:00 will appear as a bar of four columns under the relevant time.
The formula seems to work fine apart from one issue which I can seem to get right. If the activity covers a time period after 10pm, the bar is one column short, so a time period from 20:00-22:00 stops at 21:45. If I double-click into the cell with 22:00 (BO20) on the Gantt chart and then click out of it, the problem is corrected, so I don't think it is a formula error
|However, the formula I'm using is:
IF(AND($E$4=$A$195,BO$20>=$F$4,BO$19<=$H$4)),1,0
E4 is the type of activity, relating to A195 (a code, not relevant to the issue though)
F4 is the start time (20:00), H4 is the end time (22:00)
BO20 is 21:45 on the Gantt chart, BO19 is 22:00 (text is oriented to a 90 degree angle)
If there's not a problem with the formula, is it time format issue, currently [h]:mm
any advice will be gratefully received
Jed
Bookmarks