Filling the row based on criteria

In Column " E " I have login times of employees & based on this login time I wanted the complete row to be filled as shown in the spreadsheet .

Overview - Basically I wanted to see the occupancy of the seats available , each employee is scheduled for 9 hrs . Once I have this sheet filled completely , I' can then see how many seats can be shared . I'll have atleast 1000 + seats and I need something to sort this easily . Any help would be much appreciated . Thanks a lot .

Put this formula in Z3 and then copy down/across the table:

=--(AND($E3<>"",$E3<=Z$1,$F3>=Z$1))

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

In F3, copied down:

=IF(ISBLANK(E3), "", E3+(9/24)-(E3>=0.625))

In Z3 copied down and across the table:

=IF(ISBLANK($E3),0,IF($F3<$E3,--OR($F3>Z$1,$E3<=Z$1),--(AND($E3<=Z$1,$F3>=Z$1))))

Why should AC3 be 1? The seat is available at 1:30. Ditto for AE5.

Exactly. Each slot on your chart represents a 30-minute slot starting at the time listed in the column header. It represents the entire 30-minutes. So a shift that ends at 1:30 would have a "1" in the 1:00 slot which takes it up to 1:30, but not in the 1:30 slot because that represents the 30 minutes leading up to 2:00. So I think you're misreading your own chart.

To verify this...count the 1s on a row. A 9-hour shift should have 18 1s...and they do.

The only way I can get your chart to read completely correctly is to:

1) Delete column BV, it is a duplicate of column Z
2) Change the formula one more time to use some ROUNDing to eliminate the precision decimal variances.

=IF(ISBLANK($E3),0,IF($F3<$E3,--OR(ROUND($F3,5)>ROUND(Z$1,5),$E3<=Z$1),--(AND($E3<=Z$1,$F3>Z$1))))