Time Function

[maeyks]

Good pm,

Asking for help. I would like to know how to arrive at a computation wherein:

cell A1 = time IN
cell B1 = time OUT
cell c1 = number that if the times from a1 and b1 will fall between 10 pm to 6 am per hour will return as 1;
a1 = 9 pm
b1= 6 am
c1 = 8

a1 = 6 pm
b1 = 3 am
c1 = 5

condition is that it will start at 10 pm, 10:01 onwards will proceed to 11 pm counting

thanks in advance


Michael

 

[Oscar Cronquist]

Book1
	A	B	C	D	E	F
1
2		10:00 AM		9:57 AM	10:01 AM
3		6:00 PM		6:05 PM	5:58 PM
4				8:00:00	7:57:00
5
Sheet2

Formula in cell D4:
=MEDIAN($B$2,$B$3,D3)-MEDIAN($B$2,$B$3,D2)

Copy cell D4 to E4.

 

[maeyks]

Hello again,

if the entry on cell B2 is 10:00 am, how can I extract the number "10"? to put it on another cell, say C2?
Thanks

 

[Oscar Cronquist]

Formula in cell C2:
=HOUR(B2)