Forum Discussion
to ADD or REPLACE?
I'm not sure I understand,
Do you want it to stop counting the days when you add a date to column R?
Assuming that column R is today's date or a date in the past you could simply use this:
=IF(ISBLANK($H7),"", min(TODAY(),R7)-$H7)
Or you could try this:
=IF(ISBLANK($H3), "", IF(ISBLANK(R3),TODAY()-$H3,R3-H3))
Which might give you a bit more flexibility it terms of using completion dates in future.
- It Worked FANTASTIC - thank you so much!!!
A continuation of this formula, please...
This formula works:
=IF(ISBLANK($G2),"",IF(ISBLANK(S2),TODAY()-$G2,S2-G2))
K - is the countdown for G to today (the above formula located here)
& when S has a date; then, countdown stops and
is replaced with days process was worked
G - is date process started (countdown, reflected in K)
S - is date process completed (countdown changed to total days process worked,
reflected in K)
NEW PROBLEM: Now they want to remove the “#value!” in K when “N/A” (or any other text) is in G
Desired Solution:
- The “N/A” (or any other text) will remain on spreadsheet in G,
- make “N/A” (or any other text) equal to the date in I I - is date a complete formal package is submitted
- and K to reflect I “-“ minus Today as a countdown, until S is filled (as described above)
I cannot figure out how to add this new section of formula to make it all work – please advise.
errm.
You might need to edit this a bit if I haven't understood.. But I think like this:
=iferror(IF(ISBLANK($G2),"",IF(ISBLANK(S2),TODAY()-$G2,S2-G2)),I2)
this is using the iferror formula which, if our main formula results in a #value or #n/a or any error code.. will do whatever comes after the , in this case it will be the same as I2. You can just change 'I2' for whatever you want it to do, including another formula.
