Rick,

That formula worked perfect for one cell in the data set. However, I was
wondering if it were possible for it to scan through all of the records in
the dataset. I though I might be able to accomplish this in either a named
range or in an array, but I am a bit unsure.

Thanks,

-- Paul

"Paul" wrote:

Rick,

Thank you for your response. I am going to try what you have posted today.

I am sorry that I was not really clear. Now that I see what you are asking
here is a better explanation:

Task | Due | Completed
A | 1/1/2009 | 1/1/2009
B | 1/1/2009 | {BLANK}

I would like to have the function on a different sheet that would pull Task
B because there is no completed date and it was due before today. I would
like it to pull the task information (Task name (B) and the Due date
(1/1/2009) into a cell or series of cells.

If possible, I would like it to pull any task that does not have a completed
date that is due before today's date within the data set. I understand now,
that this might need a new row for each entry, which would be great as well.

Thank you again,

-- Paul

"Rick Rothstein" wrote:

I find your question to be somewhat unclear. You talk about Columns A, B, C
and D in general, but say you want your result in F3. If, as you said in an
answer to Biff later on that you could have more than one blank in Column A
to process, how did you want all that to fit in F3? If I can make a guess at
what I think you meant... assuming for any give row, Column A's value is
blank and Column B's value is a date past today (assumed to mean greater
than today), then you want the concatenation of that row's Column C and D
values placed into that row's Column F cell (with the date in Column D
looking like a date). If this is correct, put this formula F3 and copy it
down...

=IF(AND(A3="",B3TODAY()),C3&TEXT(D3," m/d/yyyy"),"")

You can control the display of the date from Column D by changing the date
pattern (" m/d/yyyy") in the TEXT function to any date pattern you want (the
TEXT function uses the same patterns as the Cell Formatting does). Note that
I put a space in front of the 'm' in the date pattern... I did this so there
would be a space between whatever is in Column C's cell and the date from
Column D... if you don't want that space in the concatenated text, just
remove it from the date pattern in the TEXT function.

--
Rick (MVP - Excel)


"Paul" wrote in message
...
I am needing to get a function to do the following:

Look down a column(A) for any blank cell, then for that blank cell look in
the cell to the right in column B and see if the date is past today. If
this
is true, I want the values of the corresponding cells in column C and D
concantenated and inserted into cell F3.

Another major issue is that the value in the cell for column D is a date
and
when it is inserted it comes as the number value instead of the typical
date.

Thank you for any help,

-- Paul