Trouble converting date while using CONCATENATE function Excel 2007

Question

I have a small spreadsheet that I pull from into another tab (sheet). I have got the CONCATENATE function working except in the date cell. I have tried all the things found wqhen searching and it just doesnt work. I am attaching the spreadsheet if anyone can help. The issues is in the printout tab in C2.

I'm using 2007 of office and windows 10 if it matters.

Thanks

Paul

sergeibaklan · Answer

Hi Paul,&nbsp;Dates in Excel are sequential&nbsp;numbers starting from January 01, 1900. Formatting the cell as date you only change the representation. In your formula Excel correctly returns 42957 what is the value for August 10, 2017.&nbsp;Use in formula TEXT(mainsheet!O3,"yyyy-mm-dd") &nbsp;instead of&nbsp;mainsheet!O3 (or with format you use for dates)

paul minichillo · Answer

Sergei,&nbsp; Thank you, I have tried that for hours and it wouldnt work. Yours did. Now I have a new question. In my printout sheet when I copy that formula down the column it shows 01-00-1900. Is there a way to stop that from displaying? I want the formula to follow whatever is entered into the cells in the mainsheet but the printout being blank unless there is something in the cell would be nice. I see this happening in other cells also like the w/o column and customer/designer. These, if blank in the mainsheet would be nice to be blank in the printout sheet. I hope this makes sense.&nbsp; Thanks again for the help.&nbsp; Paul

sergeibaklan · Answer

Paul,Simplest way is to check with IF like... &amp; IF(&lt;date&gt; &gt; 1, "-"&amp;TEXT(&lt;date&gt;, format), "") &amp;...Forgot you use CONCATENATE, thus above between two commas in it

paul minichillo · Answer

Sorry you lost me on this one.

sergeibaklan · Answer

Hi Paul,&nbsp;Okay, let start from scratch. Sorry if i repeat some basics you probably know, not sure there is your issue.&nbsp;1) As a comment - if you subscribe on Office 365 with Excel 2016 here is the much more powerful function TEXTJOIN which is more suitable for your task. But let continue with 2007.&nbsp;2) Practically everywhere you may use ampersand to concatenate the text, e.g. =CONCATENATE("a","b") and ="a" &amp; "b" return exactly the same "ab".&nbsp;&nbsp;3) In Excel there is ISBLANK functions, but you ahve to be sure there is no empty string ("") in the cell you check. There are several ways to check if the cell is blank or has empty string.&nbsp;Not thinking there is date, number or text you may compare the length&nbsp;of above with zero taking into account what LEN() works for all of them.&nbsp;&nbsp;4) That's not the only way, but let use&nbsp;LEN(). When=IF(LEN(mainsheet!D4)&gt;0, "-" &amp; mainsheet!D4, "")returns "-5" if you have number 5 in D4, otherwise empty string.&nbsp;5) Let combine for all your cells. For long formulas better to use some formatting. You may use Alt+Enter in formula bar to enter new line. Better to use some editor, even Notepad. Formula will be=CONCATENATE(
   IF(LEN(mainsheet!D4)&gt;0, "-" &amp; mainsheet!D4, ""),
   IF(LEN(mainsheet!L4)&gt;0, "-" &amp; mainsheet!L4, ""),
   IF(LEN(mainsheet!O4)&gt;0, "-" &amp; mainsheet!O4, ""),
   IF(LEN(mainsheet!Q4)&gt;0, "-" &amp; mainsheet!Q4, "")
)   &nbsp;6) Here is small issue - you always have a dash on the front of your resulting string. Not to complicate the checking we may take entire string without first symbol=MID(
   CONCATENATE(
      IF(LEN(mainsheet!D4)&gt;0, "-" &amp; mainsheet!D4, ""),
      IF(LEN(mainsheet!L4)&gt;0, "-" &amp; mainsheet!L4, ""),
      IF(LEN(mainsheet!O4)&gt;0, "-" &amp; mainsheet!O4, ""),
      IF(LEN(mainsheet!Q4)&gt;0, "-" &amp; mainsheet!Q4, "")
   ), 2, 100
)   where 2 says we take the string starting from second character and 100 is any number which is definitely&nbsp;more than length of you resulting string.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

Forum Discussion

Trouble converting date while using CONCATENATE function Excel 2007

Share

Resources