Quote from DerkLeaving aside the time format for the moment, my earlier formula and Tom's modification work because of the offset by 1 of the search range C9:C92 in the last expression. Because the two expressions (pa*1 and A.M.) are multipluied together they will return true only if the A.M. is in the next line after the pa*1 line.

Now, why does search not work for you? What version of Excel are you running?

As to time, theer is a way around that if we can assume the only cells with just numbers in the range C9:C92 are formatted as dates. Is that the case?

Hi Derk:

I'm running Excel 2003. I'm not sure why only one of Yjoshi's formulas work. The one with SEARCH returns a value of zero? I've triple checked it. I do not know why?

I'm going to try it again right now, using:

=SUMPRODUCT(ISNUMBER(SEARCH("pa*1",C8:C91,1)))

Although there is clearly a "PASTA 1" in cell C50, the above formula returns a value of zero.

Again, I do not know why.

However, using this formula, a value of 1 is returned (which is correct):

=SUMPRODUCT(ISNUMBER(FIND("pa",LOWER(C8:C91),1))*ISNUMBER(FIND(1,C8:C91,1)))

-------------------------------------------------------------------------

CELLS DIRECTLY BELOW

The cells right below are formatted as:

Custom: h:mm AM/PM

Thus, when someone types in 8:00, it auto changes to 8:00 AM.

I want to avoid having to type in military time because the entire format and all related formula cells would have to be changed, which is a much larger project than I want to get into.