Concatenate several dates and hide 1/00/00

[Jeff]

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.

---

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

[Max]

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeff" wrote:
> 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

[Jeff]

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!

"Max" wrote:

> 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)
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Jeff" wrote:
> > 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

[Max]

"Jeff" wrote:
> 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:
> > =IF(W2="","",
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")))))))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[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)



Jeff wrote:
>
> 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

--

Dave Peterson