# Return a Date based on the first Non-Blank value in a row

#### RLegGrand

##### New Member
Hello! I haven't used this forum in many, many years, but am back using Excel again and so here I am... albeit a bit rusty. Thanks for your patience.

I have a series of sequential dates in Row.1

Col.C-End I have the number of engineers assigned to a unique task on any given day.

Col.B Is the name of the task

Col.A is where I need a formula that looks for the first non-blank cell in the same row (the first date an engineer is assigned) and returns the date from Row.1 in that column.

I assume this will use some sort of Indirect statement? I can use a Match/Array formula to get the value of the first instance, but can't figure out how to pull the date from Row.1?

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### Rastaman

##### Board Regular
Perhaps something like this:

=OFFSET(\$A\$1,0,MIN(IF(C2:N2<>"",COLUMN(C2:N2)))-1,1,1)

confirmed with <ctrl><shift><enter>

Rick

#### RLegGrand

##### New Member
Hmmmm… Gives me an error.

#### RLegGrand

##### New Member
I think I see what your formula suggests, but getting an error still.

For clarity on my dataset C2:F5 (abbreviated range) contains the number of engineers assigned to a unique task each day. HOWEVER, in the four days of this example, they could be assigned to any possible variation, one day, three days, no days, alternate days.

Again, I need to find the date of the FIRST instance, but the row will have an inconsistent number of blanks vs. entries.

Hoping to solve for A:A

A B C D E F
1 Start Date Title 1/1 1/2 1/3 1/4
2 01/02/2019 Example A 1
3 01/02/2019 Example B 1 1
4 01/03/2019 Example C 4
5 01/04/2019 Example D 2

Thanks Again!

#### Rastaman

##### Board Regular

Sorry, my response got cut off. This is a CSE, or Array, formula. Instead of confirming with Enter, you need to confirm with control-shift-enter.

If I understand your spreadsheet, cells C1:N1 (or whatever you last column is) contains the sequential dates. Rows 2 downward is a list of names in column B. And you want column A to show the date in row 1 of the first non-blank cell in that row.

Rick

#### RLegGrand

##### New Member
Ahh, my oversight - That makes it work!!

Thanks so much

#### Rastaman

##### Board Regular
Glad I could help. Sorry for the earlier confusion.

Replies
11
Views
119
Replies
10
Views
184
Replies
9
Views
141
Replies
2
Views
276
Replies
15
Views
120