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

		arrival	departure	17.11.2017	18.11.2017	19.11.2017	20.11.2017	21.11.2017	22.11.2017	23.11.2017
1. case	yy						1	1	1	1
						1	1	1	1
2.case		19.11.2017.	23.11.2017.
		18.11.2017.	24.11.2017.

<tbody>
</tbody>

 

[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

=IF(AND(C$1>=$A2,C$1<$B2),"1","") BR M

<tbody>
</tbody><colgroup><col></colgroup>