Function based on blank cell and date past due

[Paul]

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

 

[Paul]

One addition is that if I cannot concantenate the cell values, having them
populate in contiguous cells would work as well.

 

[Paul]

Oh and I am using '07 version.

 

[T. Valko]

Look down a column(A) for any blank cell

Is there only one blank cell in column A?

 

[Paul]

I think that there will be many blank cells. But that is why step two must
look at a due date to see if it is overdue.

 

[T. Valko]

Ok, for every blank cell in column is there corresponding data in columns C
and D or might they also be blank?

If there are many cells that meet these conditions this will take a very
complicated formula.

 

[Paul]

No, C and D must contain data.

Ok, for every blank cell in column is there corresponding data in columns C
and D or might they also be blank?

If there are many cells that meet these conditions this will take a very
complicated formula.

 

[T. Valko]

and I am using '07 version.

Ok, this is fairly complicated so I did this the easiest and most efficient
way I could think of.

Assume the range of interest is A325.

Enter this formula in F2. This will return the count of how many records
meet the criteria of column A being blank and the corresponding date in
column B being greater than today's date.

=COUNTIFS(A3:A25,"",B3:B25,">"&TODAY())

Enter this array formula** in F3:

=IF(ROWS(F$3:F3)<=F$2,SMALL(IF((A$3:A$25="")*(B$3:B$25>TODAY()),ROW(A$3:A$25)),ROWS(F$3:F3)),"")

** 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.

Enter this formula G3:

=IF(F3="","",INDEX(C:C,F3)&" "&TEXT(INDEX(D,F3),"m/d/yyyy"))

Select both F3 and G3 then copy down until you get blanks.

 

[Rick Rothstein]

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="",B3>TODAY()),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.

 

[Paul]

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

 

[Paul]

I will try this today.

Thank you for the response.

-- Paul

 

[Paul]

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