Trouble converting date while using CONCATENATE function Excel 2007

Hi Paul,

Okay, let start from scratch. Sorry if i repeat some basics you probably know, not sure there is your issue.

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.

2) Practically everywhere you may use ampersand to concatenate the text, e.g. =CONCATENATE("a","b") and ="a" & "b" return exactly the same "ab". 

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. Not thinking there is date, number or text you may compare the length of above with zero taking into account what LEN() works for all of them.

4) That's not the only way, but let use LEN(). When

=IF(LEN(mainsheet!D4)>0, "-" & mainsheet!D4, "")

returns "-5" if you have number 5 in D4, otherwise empty string.

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)>0, "-" & mainsheet!D4, ""),
   IF(LEN(mainsheet!L4)>0, "-" & mainsheet!L4, ""),
   IF(LEN(mainsheet!O4)>0, "-" & mainsheet!O4, ""),
   IF(LEN(mainsheet!Q4)>0, "-" & mainsheet!Q4, "")
)   

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)>0, "-" & mainsheet!D4, ""),
      IF(LEN(mainsheet!L4)>0, "-" & mainsheet!L4, ""),
      IF(LEN(mainsheet!O4)>0, "-" & mainsheet!O4, ""),
      IF(LEN(mainsheet!Q4)>0, "-" & mainsheet!Q4, "")
   ), 2, 100
)   

where 2 says we take the string starting from second character and 100 is any number which is definitely more than length of you resulting string.