Function or Formula to Return a Date When a Non-zero Value is Found in a Row

Try these...

Enter this array formula** in P4:

=IFERROR(INDEX(C$3:N$3,MATCH(TRUE,C4:N4>0,0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format in the date style of your choice.

Enter this formula in Q4:

=IFERROR(LOOKUP(2,1/C4:N4,C$3:N$3),"")

Format in the date style of your choice.

Select P4:Q4 and copy down as needed.

I was trying to avoid the use of array formulas since it sounds like you have a lot of data. Use Tony's Q4 formula, as mine was pretty much the same, but for P4, try this:
=EOMONTH(Q4,-COUNTIF(C4:N4,">0")+1)

Now, mine is making an assumption, in that the numbers are in blocks with no zero/blank values between the beginning and the end. Also, your calculation of the months up top by always adding 31 is going to cause problems as you expand that out a few years. But, I assumed you would have correct dates in your final spreadsheet.

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

Removed original text. Please ignore.