Concatenate several dates and hide 1/00/00

[Guest]

I have this formula in BG2:
=IF(AND(W2=""),CONCATENATE(TEXT(K2,"m/dd/yy"),CHAR(10),TEXT(O2,"m/dd/yy"),CHAR(10),TEXT(S2,"m/dd/yy"),CHAR(10)))

When there are no dates in the referring cells (K2, O2, S2) it returns:
1/00/00
1/00/00
1/00/00
Is there a way to hide this?
I already tried leading the formula with: =IF(K2=0,"",
which works good until I enter a date (I used 6/13/94) in K2, it retuns:
6/13/94
1/00/00
1/00/00

 

[Bryan Hessey]

Try a variation of

=IF(W2="",CONCATENATE(IF(K2="","",TEXT(K2,"m/dd/yy")),CHAR(10),IF(O2="","",TEXT(O2,"m/dd/yy")),CHAR(10),IF(S2="","",TEXT(S2,"m/dd/yy")),CHAR(10)),"w2
not blank")

the 'And' test for W2 held only 1 condition and was removed.

---

 

[Guest]

Jeff,

We could also use the ampersand operator "&" to concat strings in a cell
instead of CONCATENATE (less to type, a shorter formula)
[eg: in C1: =A1&" "&B1 is same as putting in C1:=CONCATENATE(A1," ",B1)]

A little klunky perhaps, but think you could try the formula below in BG2,
with BG2 formatted to wrap text, as mentioned before:
[Note that the formula is intentionally line-broken for clarity on the
required IF testing / levels]

=IF(W2="","",
IF(AND(K2="",O2="",S2=""),"",
IF(AND(K2<>"",O2="",S2=""),TEXT(K2,"m/dd/yy"),
IF(AND(K2<>"",O2<>"",S2=""),TEXT(K2,"m/dd/yy")&CHAR(10)&TEXT(O2,"m/dd/yy"),
IF(AND(K2<>"",O2="",S2<>""),TEXT(K2,"m/dd/yy")&CHAR(10)&TEXT(S2,"m/dd/yy"),
IF(AND(K2="",O2<>"",S2<>""),TEXT(O2,"m/dd/yy")&CHAR(10)&TEXT(S2,"m/dd/yy"),
TEXT(K2,"m/dd/yy")&CHAR(10)&TEXT(O2,"m/dd/yy")&CHAR(10)&TEXT(S2,"m/dd/yy")))))))

(Paste the above formula directly into the formula bar for BG2)

 

[Guest]

Hello Max,
I have already started working with Bryan's method and it's working great.
I'll give yours a try out of curiosity, but for now I'll keep working with
Bryan's.
Thanks to both of you for all your help!

Jeff,

We could also use the ampersand operator "&" to concat strings in a cell
instead of CONCATENATE (less to type, a shorter formula)
[eg: in C1: =A1&" "&B1 is same as putting in C1:=CONCATENATE(A1," ",B1)]

A little klunky perhaps, but think you could try the formula below in BG2,
with BG2 formatted to wrap text, as mentioned before:
[Note that the formula is intentionally line-broken for clarity on the
required IF testing / levels]

=IF(W2="","",
IF(AND(K2="",O2="",S2=""),"",
IF(AND(K2<>"",O2="",S2=""),TEXT(K2,"m/dd/yy"),
IF(AND(K2<>"",O2<>"",S2=""),TEXT(K2,"m/dd/yy")&CHAR(10)&TEXT(O2,"m/dd/yy"),
IF(AND(K2<>"",O2="",S2<>""),TEXT(K2,"m/dd/yy")&CHAR(10)&TEXT(S2,"m/dd/yy"),
IF(AND(K2="",O2<>"",S2<>""),TEXT(O2,"m/dd/yy")&CHAR(10)&TEXT(S2,"m/dd/yy"),
TEXT(K2,"m/dd/yy")&CHAR(10)&TEXT(O2,"m/dd/yy")&CHAR(10)&TEXT(S2,"m/dd/yy")))))))

(Paste the above formula directly into the formula bar for BG2)

---

I have this formula in BG2:
=IF(AND(W2=""),CONCATENATE(TEXT(K2,"m/dd/yy"),CHAR(10),TEXT(O2,"m/dd/yy"),CHAR(10),TEXT(S2,"m/dd/yy"),CHAR(10)))

When there are no dates in the referring cells (K2, O2, S2) it returns:
1/00/00
1/00/00
1/00/00
Is there a way to hide this?
I already tried leading the formula with: =IF(K2=0,"",
which works good until I enter a date (I used 6/13/94) in K2, it retuns:
6/13/94
1/00/00
1/00/00

 

[Guest]

Hello Max,
I have already started working with Bryan's method and it's working great.
I'll give yours a try out of curiosity, but for now I'll keep working with
Bryan's.
Thanks to both of you for all your help!

No problem, glad to hear you got it working !

To be consistent with Bryan's interp of your orig. post,
just change this 1st part of the suggested formula:to:
=IF(W2<>"","W2 is not empty",

viz, use instead in BG2:
=IF(W2<>"","W2 is not empty",
IF(AND(K2="",O2="",S2=""),"",
IF(AND(K2<>"",O2="",S2=""),TEXT(K2,"m/dd/yy"),
IF(AND(K2<>"",O2<>"",S2=""),TEXT(K2,"m/dd/yy")&CHAR(10)&TEXT(O2,"m/dd/yy"),
IF(AND(K2<>"",O2="",S2<>""),TEXT(K2,"m/dd/yy")&CHAR(10)&TEXT(S2,"m/dd/yy"),
IF(AND(K2="",O2<>"",S2<>""),TEXT(O2,"m/dd/yy")&CHAR(10)&TEXT(S2,"m/dd/yy"),
TEXT(K2,"m/dd/yy")&CHAR(10)&TEXT(O2,"m/dd/yy")&CHAR(10)&TEXT(S2,"m/dd/yy")))))))

---

 

[Dave Peterson]

One more option:

=SUBSTITUTE(TRIM(IF(W2<>"","not blank",IF(K2="","",TEXT(K2,"m/dd/yy"))&" "&
IF(O2="","",TEXT(O2,"m/dd/yy"))&" "&IF(S2="","",TEXT(S2,"m/dd/yy")))),
" ",CHAR(10))

(all one cell)