Populate cells based off column headers

[Davers]

Hey Gang! It's been a few years! I have a spreadsheet with a start time in cell A2, and a stop time in cell B2. I have 24 columns with the Hour as the header. So column C1 = 1:00 AM, D1 = 2:00 AM etc etc. What I'd like to do is populate the cells under the hours with a '1' for the hours that correspond to the start time, the stop time, and the hours in between. So, as an example:

A2 = 6:43 AM
B2 = 3:10 PM

I'd like a 1 in the cells for the hours of 6:00 AM - 3:00 PM.

I've been scratching my head for a day and I still can't figure out how to do it..

Thanks for any help!

Dave

 

[steve the fish]

You should really start at 12:00 AM. Then you can use in C2 and copy across:

=IF(AND(C1>=FLOOR($A2,1/24),C1<=FLOOR($B2,1/24)),1,"")

 

[steve the fish]

This maybe better in case of rounding error:

=IF(AND(FLOOR(C1,1/24)>=FLOOR($A2,1/24),FLOOR(C1,1/24)<=FLOOR($B2,1/24)),1,"")

 

[BGY23]

As stated by Steve you should have from 00:00 in C1. This formula is based on that and assumes you're entering the data in time format.

[TABLE="width: 353"]
<colgroup><col width="471" style="width: 353pt; mso-width-source: userset; mso-width-alt: 15072;"> <tbody>[TR]
[TD="width: 471, bgcolor: transparent"][TABLE="width: 386"]
<colgroup><col width="515" style="width: 386pt; mso-width-source: userset; mso-width-alt: 16480;"> <tbody>[TR]
[TD="width: 515, bgcolor: transparent"]=IF(AND(HOUR(C$1)>=HOUR($A2),HOUR(C$1)<=HOUR($B2)),1,0)

Copyriht and down to fill your sheet

[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Davers]

Wow!! Thanks guys! I had forgotten how awesome this forum is! I've changed my spreadsheet to put 00:00 first and the formula works perfectly!

Thanks again for the quick answer from both of you!

Dave

As stated by Steve you should have from 00:00 in C1. This formula is based on that and assumes you're entering the data in time format.

[TABLE="width: 353"]
<colgroup><col width="471" style="width: 353pt; mso-width-source: userset; mso-width-alt: 15072;"> <tbody>[TR]
[TD="width: 471, bgcolor: transparent"][TABLE="width: 386"]
<colgroup><col width="515" style="width: 386pt; mso-width-source: userset; mso-width-alt: 16480;"> <tbody>[TR]
[TD="width: 515, bgcolor: transparent"]=IF(AND(HOUR(C$1)>=HOUR($A2),HOUR(C$1)<=HOUR($B2)),1,0)

Copyriht and down to fill your sheet

[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]