Date range

[Marin30]

Hello everyone,

How I can get:

1. case: arrival and departire date useing criterion 1 and date above
AND
In the other direction
2. case: need to get 1 in table using dates left and above?

thank you very much
[TABLE="width: 578"]
<tbody>[TR]
[TD="class: xl71, width: 48, bgcolor: transparent"][/TD]
[TD="class: xl71, width: 21, bgcolor: transparent"][/TD]
[TD="class: xl71, width: 80, bgcolor: transparent"]arrival
[/TD]
[TD="class: xl71, width: 80, bgcolor: transparent"]departure
[/TD]
[TD="class: xl72, width: 77, bgcolor: transparent"]17.11.2017
[/TD]
[TD="class: xl72, width: 77, bgcolor: transparent"]18.11.2017
[/TD]
[TD="class: xl72, width: 77, bgcolor: transparent"]19.11.2017
[/TD]
[TD="class: xl72, width: 77, bgcolor: transparent"]20.11.2017
[/TD]
[TD="class: xl72, width: 77, bgcolor: transparent"]21.11.2017
[/TD]
[TD="class: xl72, width: 77, bgcolor: transparent"]22.11.2017
[/TD]
[TD="class: xl72, width: 77, bgcolor: transparent"]23.11.2017
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]1. case
[/TD]
[TD="class: xl68, bgcolor: transparent"]yy
[/TD]
[TD="class: xl73, bgcolor: yellow"][/TD]
[TD="class: xl73, bgcolor: yellow"][/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"]1
[/TD]
[TD="class: xl77, bgcolor: transparent"]1
[/TD]
[TD="class: xl77, bgcolor: transparent"]1
[/TD]
[TD="class: xl77, bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: yellow"][/TD]
[TD="class: xl73, bgcolor: yellow"][/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"]1
[/TD]
[TD="class: xl77, bgcolor: transparent"]1
[/TD]
[TD="class: xl77, bgcolor: transparent"]1
[/TD]
[TD="class: xl77, bgcolor: transparent"]1
[/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] 2.case
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"]19.11.2017.
[/TD]
[TD="class: xl78, bgcolor: transparent"]23.11.2017.
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: yellow"][/TD]
[TD="class: xl74, bgcolor: yellow"][/TD]
[TD="class: xl74, bgcolor: yellow"][/TD]
[TD="class: xl74, bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"]18.11.2017.
[/TD]
[TD="class: xl78, bgcolor: transparent"]24.11.2017.
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: yellow"][/TD]
[TD="class: xl74, bgcolor: yellow"][/TD]
[TD="class: xl74, bgcolor: yellow"][/TD]
[TD="class: xl74, bgcolor: yellow"][/TD]
[TD="class: xl74, bgcolor: yellow"][/TD]
[TD="class: xl74, bgcolor: yellow"][/TD]
[/TR]
</tbody>[/TABLE]

 

[pkdeimos]

Hi,

Assumption: Data range is A1:K6

Case 2 is relatively easier. Please try this formula in E5 and copy it to the other cells (E5:K6)

=IF(AND(E$1>=$C5,E$1<$D5),1,"")


I used array formula in C2 and D2 for Case 1
C2 = MIN(IF(E2:K2=1,$E$1:$K$1,MAX($E$1:$K$1)))
D2 = MAX($E$1:$K$1*E2:K2)+1

Copy these array formulae to C3:D3. I would also like to know if there is a better way to do case 1.

Cheers

 

[Marin30]

Hello,

I have get follow fromula:

1. case:
Arrival
=IF(A$1="arrival",INDEX($C$1:$J$1,1,MATCH(1,$C2:$J2,0)),INDEX($C$1:$J$1,1,MATCH(9.99999999999999E+307,$C2:$J2)))
Departure
=IF(B$1="arrivak",INDEX($C$1:$K$1,1,MATCH(1,$C2:$K2,0)),INDEX($C$1:$K$1,1,MATCH(9.99999999999999E+307,$C2:$K2)+1))

2. Case
[TABLE="width: 100"]
<tbody>[TR]
[TD]=IF(AND(C$1>=$A2,C$1<$B2),"1","")

BR

M[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]