Forum Discussion
to ADD or REPLACE?
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.
Your suggested formula is almost working Phillip…. It does work in every scenario except, when there is text in G then, K produces an unexpectedly huge result – way more than expected from TODAY()-$I2,S2-I2.
When I modify the above formula to:
=iferror(IF(ISBLANK($G2),"",IF(ISBLANK(S2),TODAY()-$G2,S2-G2)),IF(ISBLANK(S2),TODAY()-$I2,S2-I2)) I get the same unexpected large result.
What else can I try, please?
what do you want it to do if there is text in G?
can you paste the contents of a row thats not working?
