How to lookup first non-blank value, and use date in corresponding cell in formula

[germandiazm]

Can anyone help me with the following excel problem?

I have a table that has dates on the first column an ID number in the first row and on the second row we write down how many days left from today's date.
The days left correspond to the dates on the first column and we are doing that manually on a daily basis.
What formula can I use in B2:F2 that would search the first non-blank cell in that column, look for the date on the same row on column A and subtract that date from today's date?
If date has already past, return value to be 0.

	A	B	C	D	E	F
1	Car	01	02	03	04	05
2	Days until departure	0	1	4	6	8
3	1/06/2018
4	2/06/2018
5	3/06/2018	43
6	4/06/2018	23
7	5/06/2018	54
8	6/06/2018		45
9	7/06/2018		12
10	8/06/2018		43
11	9/06/2018			43
12	10/06/2018			34
13	11/06/2018				23
14	12/06/2018				23
15	13/06/2018					23
16	14/06/2018					65

<tbody>
</tbody>

Thanks,
germandiazm

 

[Oscar Cronquist]

	A​	B​	C​	D​	E​	F​
1​	Car	1​	2​	3​	4​	5​
2​	Days until departure	-2​	1​	4​	6​	8​
3​	6/1/2018​
4​	6/2/2018​
5​	6/3/2018​	43​
6​	6/4/2018​	23​
7​	6/5/2018​	54​
8​	6/6/2018​		45​
9​	6/7/2018​		12​
10​	6/8/2018​		43​
11​	6/9/2018​			43​
12​	6/10/2018​			34​
13​	6/11/2018​				23​
14​	6/12/2018​				23​
15​	6/13/2018​					23​
16​	6/14/2018​					65​

Sheet: Sheet73

Array formula in cell B2:
=INDEX($A$3:$A$16,MATCH(FALSE,ISBLANK(B$3:B$16),0))-TODAY()

 

[germandiazm]

Thanks Oscar,

I used the array formula above and all the results are zero and can't figure out why.

Also, I need the value to be 0 or higher, negative values (like in column B) need to be zero as well. Will IF work in an array formula?

{=IF((INDEX($A$3:$A$16,MATCH(FALSE,ISBLANK(B$3:B$16),0))-TODAY())<0,"0",(INDEX($A$3:$A$16,MATCH(FALSE,ISBLANK(B$3:B$16),0))-TODAY()))}


Thanks

 

[Oscar Cronquist]

I used the array formula above and all the results are zero and can't figure out why.

Did you enter the formula as an array formula?

1. Type the formula
2. Press and hold CTRL + SHIFT
3. Press Enter

The formula begins with a curly bracket {=formula} and ends with a curly bracket in the formula bar if you succeded.

Also, I need the value to be 0 or higher, negative values (like in column B) need to be zero as well. Will IF work in an array formula?

Try this array formula:

=MAX(INDEX($A$3:$A$16,MATCH(FALSE,ISBLANK(B$3:B$16),0))-TODAY(),0)

 

[Peter_SSs]

You could also try this standard-entry formula, copied across, provided you are using Excel 2010 or later.
After entering the first formula, you may have to re-format that cell as Number or General as the formula may initially coerce it to Date. Copying across then should maintain that format for the other cells.

Excel Workbook
	A	B	C	D	E	F
1	Car	1	2	3	4	5
2	Days until departure	0	0	3	5	7
3	1/06/2018
4	2/06/2018
5	3/06/2018	43
6	4/06/2018	23
7	5/06/2018	54
8	6/06/2018		45
9	7/06/2018		12
10	8/06/2018		43
11	9/06/2018			43
12	10/06/2018			34
13	11/06/2018				23
14	12/06/2018				23
15	13/06/2018					23
16	14/06/2018					65
Date