Using Time Range to Create Gantt Chart

[craiglisterman]

I'm trying to use if/and functions to create a gantt chart for a schedule.

A person working from 8:00 am - 5:00 pm, where if its outside this time range its a 0, inside its a 1. Conditional formatting to do the rest.

X axis is the day broken down into hours, y axis is the names of people.

I've been trying to make a unique formula for each box but they always conflict, This was one attempt but it never worked. Where c5 was start time, c6 was end time.

=IF(C5>TIME(17,0,0), "0", IF(C5<TIME(17,0,0), "1", if(C5=TIME(17,0,0), "1",IF(C6>TIME(17,0,0), "1", IF(C6<TIME(17,0,0), "0", if(C6=TIME(17,0,0), "0"))))))

I know it can be done, please help!

 

[RickXL]

Hi and welcome to the forum.

I would assign hours to the cells in your chart then see if that time was between the start and end or not. As we are only working in whole hours then I would set the time for the cells to be on the half hour.

For instance cell M2 might be set to 0.5, Cell N2 to 1.5, O2 to 2.5 etc. The test would then be: are the cell's contents between the start and finish times.

To convert the contents to Excel times you would need to divide by 24.

With that as the basic idea we could calculate the notional contents of the cells by using the COLUMN() function. For instance, if M (col 13) was your start column:

=(COLUMN()-13)/24
would calculate the cell's central time

The complete test would be:
=AND(((COLUMN()-12.5)/24)>$C2,((COLUMN()-12.5)/24)<$D2)

Note: I put start and finish times on the same row C2 and D2, respectively.