View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
Jeff Jeff is offline
external usenet poster
 
Posts: 921
Default 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