Forum Discussion
to ADD or REPLACE?
what do you want it to do if there is text in G?
can you paste the contents of a row thats not working?
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).
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.
correction on ultimate goal:
# of days worked between G or I dates & S
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
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.
Lets try a visual...
:( 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.