Forum Discussion
to ADD or REPLACE?
Lets try a visual...
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.
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