datedif & freeze

[Guest]

Hello Gurus and newsgroup users, your kind assistance please

I need to create a function or formula that will return the date difference between a start date (SD) + a number that represent the number of days to complete that task (task), then find the difference from todays date (today()). I would also need the answer to return a negative figure if the task has taken longer than the task (task) dates has specified.

Example

(Start date) + (task days) - (todays date) = return positive or negative figure in days
=(sd + task - today()

But here's the really tricky bit: I need the returned value to freeze depending on a value in another cell has been entered. (eg If in the completed column, Yes has been entered

Any help would be appreciated

Many thank

Pau

 

[Frank Kabel]

Hi
what do you mean with freeze?. Would it be sufficient to make the cell
blank or to calculate a value based on the completed cell?. e.g.
with E1 your completion date
=IF(E1<>"","completed",B1-A1)

--
Regards
Frank Kabel
Frankfurt, Germany

Hello Gurus and newsgroup users, your kind assistance please.

I need to create a function or formula that will return the date

difference between a start date (SD) + a number that represent the
number of days to complete that task (task), then find the difference
from todays date (today()). I would also need the answer to return a
negative figure if the task has taken longer than the task (task) dates
has specified.

Example

(Start date) + (task days) - (todays date) = return positive or negative figure in days.
=(sd + task - today())

But here's the really tricky bit: I need the returned value to freeze

depending on a value in another cell has been entered. (eg If in the
completed column, Yes has been entered)

 

[JE McGimpsey]

One way:

You'll need to use (an intentional) circular reference.

Choose Tools/Options/Calculation and check the Iterations checkbox.

Assume start date in A1, task days in B1 and the completed column is
column C:


D1: =IF(C1="Yes",D1,A1+B1-TODAY())