Forum Discussion
to ADD or REPLACE?
I'm trying to add another function to an existing step.
Currently, I have a properly functioning formula of: =IF(ISBLANK($H7), "", TODAY()-$H7)
which, when date column H is blank, so also, is days old column J.
AND, when a date is entered in H, the days old populates with the number of days - as desired.
Now begins my challenge:
Attempting to ADD a function to "REPLACE" days old ( which is continuously counting days)
even when Date is entered into R (task completed).
Without the above formula, the =DATEIF(H7,R7,"j") formula works fine.
MY REQUEST IS: HOW CAN I PUT THE TWO TOGETHER OR REPLACE THE OLD WITH THE NEW WHEN COLUMN J DATE IS ENTERED, please?
13 Replies
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.
