Forum Discussion
Trouble converting date while using CONCATENATE function Excel 2007
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 jus...
Hi Paul,
Dates in Excel are sequential 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.
Use in formula TEXT(mainsheet!O3,"yyyy-mm-dd") instead of mainsheet!O3 (or with format you use for dates)
Sergei,
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.
Thanks again for the help.
Paul
- Paul,
Simplest way is to check with IF like
... & IF(<date> > 1, "-"&TEXT(<date>, format), "") &...
Forgot you use CONCATENATE, thus above between two commas in it- Sorry you lost me on this one.
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.