to ADD or REPLACE?

[david.wheelock]

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?

[#2]

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.

[#3]

It Worked FANTASTIC - thank you so much!!!

[#4]

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:

1. The “N/A” (or any other text) will remain on spreadsheet in G,
2. make “N/A” (or any other text) equal to the date in I                                                                               I - is date a complete formal package is submitted
3. 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.

[#5]

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.

[#6]

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?

[#7]

what do you want it to do if there is text in G?

can you paste the contents of a row thats not working?

[#8]

If there is text in G, then I need to use the date in I to either do the countdown in K or the days worked if date in S.  IF there is no date in I or S then I need to see a number zero in K.

As I keep modifying the formula to:

=IFERROR(IF(ISBLANK($G3),"",IF(ISBLANK(S3),TODAY()-$G3,S3-G3)),IF(ISBLANK(I3),I3,(TODAY()-$I3,S3-I3)))

Then I get “0” in K with I being blank – which is good!

However, when a date is in I, the “#value!” returns, with or without a date in S - instead of the desired countdown (with out date in S), or days worked (with date in S).

[#9]

:( i've got a bit lost trying to visulaise it all.

Can you use something like if(isnumber(g3),true,false) to change the behavior depending on g3.. dates are recorded as a number, even if they are formatted to look like something else, so if isnumber is true you can assume that its a date, or know that its not text.

[#10]

Lets try a visual...

[#11]

Aaha! thanks. that's what we need!

try this:

=IFERROR(IF(ISBLANK($G3),"",IF(ISBLANK(S3),TODAY()-$G3,S3-G3)),IF(ISBLANK(S3),TEXT(TODAY()-$I3,"dd-mmm-yy"),S3-I3))

the very large result is how excel records dates.. its the number of seconds since 01/01/1900 (?) or something like that anyway. So what you need to do is tell excel to show it as a date.. but if you do that for the column then it will try to show the other numbers as a date.. so if we wrap that bit of the formula up in a text() then we can set the format for just that part of the result. I've used "dd-mmm-yy" as it matches the format of other dates in your sheet. You can change it to whatever makes sense.

[#12]

Man I am learning so much from you Phil – thank you, for your patience with me!

However, in this case, K is not to be a Date (dd-mmm-yy), it is to be a number of days (the difference between one of four possibilities) in the following columns:

Column	G	I	K	S
	Date Vacated	Official Start Date	# of Days Countdown –or-Worked (Location of this formula)	Date Completed
1)	date	blank	Countdown Between G date & TODAY()	blank
2)	Text - “Anticipated Vacancy”	blank	a zero, “0”	blank
3)	date	blank	# of days worked between S & G dates	date
4)	Text - “N/A”	date	Countdown Between I date & TODAY()	blank
Ultimate end result for all rows will be a:			# of days worked between S or I dates & G	date

[#13]

correction on ultimate goal:

# of days worked between G or I dates & S

[#14]

Ok, so.. this works I think:

=IF(ISBLANK($G2),"",IF(ISBLANK(S2),IF(ISNUMBER(G2),TODAY()-$G2,IF(G2="Anticipated Vacancy",0,TODAY()-I2)),S2-G2))

But its not very robust. As long as you stick to the 'rules' as outlined in your last post this should do what you want.

I'm pretty sure there is a neater way of doing this, but right now I cant think of it.