Hi. I hope someone has a solution to this problem. I am trying to make a schedule to be able to give a date when work will be completed.

I have 30 task fields,e.g. Task 1, Task 2, Task 3, etc. I want to create a formula that if one of the task's field is empty or = 0, then the calculation needs to go to the previous task field that has a value >1 in it and do the calculation from that task. Such as:

Start date field: 9/12/2005.

Task 1 has value 2 in the field (as in Task 1 will take 2 days to complete). So it will be 9/14/2005 before Task 2 can be started.

Task 2 has 1 in its field so that means Task 2 will take 1 day to complete. So it will be 9/15/05 before the next task can be started.

Task 3 is empty or equals 0.

Task 4 is empty or equals 0

Then Task 5 will have 1 in the field, but it needs to look first at value in Task 4, if finds it empty or = 0, then go to Task 3, if finds that empty or = 0, go on to Task 2 and take the value from field in Task 2 to make its calculation.

The resulting value would be if start date is 9/12/05 and Task 1 has the value of 2, the Task 2 could be started on 9/14/05. No value in Task 3 or Task 4 but Task 5 has 1 in its field so Task 5 will be completed in 1 day or 9/16/05. I have worked out all my date calculations. It is just this 1 formula I can't get to work.

Many thanks in advance for any help you can provide.

I might be missing the point here but doesn't the following calculation give you the Completion Date?

Completion Date = Start Date + Task1 + Task2 + (and so on up to) + Task30

It seems to me that any task will be completed on StartDate + sum of all durations up to and including this task - whether some of them are empty, zero or not.

However, you should consider moving the tasks into a related file, where each task would be a record.

Thank you for your response. I had a nested "blankfield" in my calculation that messed me up.

I appreciate your response and your solution works for me very often. I'm glad to say my problem is solved. Thank you.