Concatenate several dates and hide 1/00/00
 

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

Concatenate several dates and hide 1/00/00
 

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.

---

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


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=559160

Concatenate several dates and hide 1/00/00
 

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

Concatenate several dates and hide 1/00/00
 

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

Concatenate several dates and hide 1/00/00
 

"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
---

Concatenate several dates and hide 1/00/00
 

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