Complex If/Then Statements

[Lora66]

I'm creating a type of checklist in Excel where I have the general milestone/task listed in a column with all of the individual steps to complete the task listed under it.

What I want is after the completion date of every individual step has been entered in Column E, for today's date [TODAY()] to be automatically entered into the column E next to the Milestone heading.

I've tried conditional formatting and if/then nesting, but I can't seem to get it to work right. Any suggestions? Thanks!

For example:

Column D: Column E: (Actual Completion Date)

Milestone 1: (automatically enter 09/28/2011 as completion date)

Step 1: 09/27/2011
Step 2: 09/28/2011

 

[P_Guru]

So, after the user enters a date in column E, you want to overwrite it with today's date?

 

[Lora66]

So, after the user enters a date in column E, you want to overwrite it with today's date?

I want it to overwrite only in certain rows in column E that are in conjunction with my task heading.

For example, the user can type any date into E5 and E6 b/c those are the rows where steps 1 (D5) and 2 (D6) are. But, once all two steps have dates entered, then I want today's date (the assumed completion date of the entire task) to be automatically triggered to be entered in E4, the row where the task heading is located.

 

[P_Guru]

OK,although I'd have thought you wanted the latest date in the section, not today's.

Anyway...the trigger point is when all fields are non blank, so suggest using countif.

 

[Lora66]

The latest date in the section would work too.

Could you give me an example of how you would write the formula with CountIf with both TODAY() and the "lastest date" as the resulting value?

 

[P_Guru]

=IF(NOT(COUNTIF(D7:D10,"")),TODAY())
=IF(NOT(COUNTIF(D7:D10,"")),MAX(D7:D10))

Assumes that

a) You know how many steps (in this case, 4)
b) Dates are the only entered values (use data validation to sort this).