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

G

germandiazm

New Member
Joined
May 9, 2017
Messages
11
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.

[TABLE="width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Car[/TD]
[TD]01[/TD]
[TD]02[/TD]
[TD]03[/TD]
[TD]04[/TD]
[TD]05[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Days until departure[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/06/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2/06/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3/06/2018[/TD]
[TD]43[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4/06/2018[/TD]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]5/06/2018[/TD]
[TD]54[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]6/06/2018[/TD]
[TD][/TD]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]7/06/2018[/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]8/06/2018[/TD]
[TD][/TD]
[TD]43[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]9/06/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]43[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]10/06/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]34[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]11/06/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]12/06/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]13/06/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]14/06/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]65[/TD]
[/TR]
</tbody>[/TABLE]


Thanks,
germandiazm
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]Car[/td][td]
1​
[/td][td]
2​
[/td][td]
3​
[/td][td]
4​
[/td][td]
5​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]Days until departure[/td][td]
-2​
[/td][td]
1​
[/td][td]
4​
[/td][td]
6​
[/td][td]
8​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]
6/1/2018​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]
6/2/2018​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]
6/3/2018​
[/td][td]
43​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]
6/4/2018​
[/td][td]
23​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]
6/5/2018​
[/td][td]
54​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]
6/6/2018​
[/td][td][/td][td]
45​
[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]
6/7/2018​
[/td][td][/td][td]
12​
[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]
6/8/2018​
[/td][td][/td][td]
43​
[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]
6/9/2018​
[/td][td][/td][td][/td][td]
43​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]
6/10/2018​
[/td][td][/td][td][/td][td]
34​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td]
6/11/2018​
[/td][td][/td][td][/td][td][/td][td]
23​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td]
6/12/2018​
[/td][td][/td][td][/td][td][/td][td]
23​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td]
6/13/2018​
[/td][td][/td][td][/td][td][/td][td][/td][td]
23​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td]
6/14/2018​
[/td][td][/td][td][/td][td][/td][td][/td][td]
65​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet73[/td][/tr][/table]

Array formula in cell B2:
=INDEX($A$3:$A$16,MATCH(FALSE,ISBLANK(B$3:B$16),0))-TODAY()
 
Upvote 0
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
 
Upvote 0
I used the array formula above and all the results are zero and can't figure out why.
Click to expand...
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?
Click to expand...

Try this array formula:

=MAX(INDEX($A$3:$A$16,MATCH(FALSE,ISBLANK(B$3:B$16),0))-TODAY(),0)
 
Upvote 0
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
ABCDEF
1Car12345
2Days until departure00357
31/06/2018
42/06/2018
53/06/201843
64/06/201823
75/06/201854
86/06/201845
97/06/201812
108/06/201843
119/06/201843
1210/06/201834
1311/06/201823
1412/06/201823
1513/06/201823
1614/06/201865
Date
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
310
dshtesy
D
A
  • Solved
Count entry by time
Replies
3
Views
194
admvlad
A
S
  • Solved
Lookup With Gaps
Replies
5
Views
216
Steve 1962
S
S
  • Question Question
Yearly Interest Calculation
Replies
1
Views
324
signup
S
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
499
etaf
etaf

Forum statistics

Threads
1,223,785
Messages
6,174,539
Members
452,571
Latest member
MarExcelTips

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top