Formula to compare monthly due dates

Hello,

I need a formula that will: look at several columns, determine what month is listed in the columns and compare dates in separate columns (see below).

A B C D E F G
1 Name 1/10/10 1/9/10 3/10/10 3/7/10 6/10/10

The formula needs to look at B, D, F to determine what month the project is due and if it has already been completed (C, E, G). If it hasn't been completed then on a separate sheet it would list by month the project due date.

A B C D E F G
January February March April May June July
Name

Nothing would return for Jan-May since they have been completed.

I would appreciate any help. Thanks.

Can you show what code have you already written, and where you are stuck?

I have tried IF, COUNTIF, ((cell>cell, IF cell=Month,1, Name, cell, "")), other versions that didn't work and I trashed the paper I had them written on.

The data above looks vey confusing. For instance, whether A-G are excel col names, or data in your first row?
Can you pl. post an excel with your data?
thx
Ajay

On the second worksheet, if Resident 1 has a plan due in January, it would show the Resident's name. If the "date completed" column has a date, the formula would not show the Resident's name.

if you are looking only for a formula, I may not be able to help.
If you are willing to write a macro, I can try to help.

I'm game if you are, my VBA skills are not very strong.

Even I am a newbie, but I am sure this is not un-achievable.
As first step, can you come up with a logical flow (in non-technical terms) to achieve what you want?

Look at the due date columns, if the due date is older than the date in the completed column then put the Resident's name on the second worksheet under the correct month. So if Mr. Smith is due for a 3 month plan in January and the date completed column is blank then his name will appear on the second worksheet under the January column.

OK, these will be the steps:-

Loop through all used rows in sheet 1.
For each row,
if due date < completed date then
get month name from due date column
search for month name in the second sheet's row 1
in the column of this month, find the last used row and put resident's name on next
row
end if
next

I would let you start trying to write the code for each of the steps. You can always google for syntax.

hth
Ajay

I will try this, thank you.