Posted to microsoft.public.excel.misc
|
|
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
|