Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Why can't dates concatenate in Spreadsheets like workbook

Answered
Request Demo
why can't we concatenate two cells designated as dates in spreadsheets like workbook? I'm working on transitioning to spreadsheets and the i'm trying to create our dates page. I cannot concatenate two cells so produce the output "September 8, 2018 (12 weeks)". The date gets turned into a number (43351). 
I

1

Comments

3 comments

Date Votes
  • Mike
    Hi Caroline,

    Thanks for the question. The reason you can't concatenate using a date is that the date is actually represented as a numeric date value, which is what the formula looks at and then pulls into the result. This is why you see "43351" for the date of September 8, 2019, i.e. 43,351 days since the year 1900. You get the same result in Excel when using dates.

    To get around this, you simply need to nest a TEXT formula within your CONCATENATE to control the resulting format of your date. For example, to achieve what you are looking for, I added the following formula syntax: TEXT(CELL,"MMMM D, YYYY ")

    And here is how my formula works when nested:



    Does this make sense? Let me know if this doesn't work for you or you have any further questions for me. Thanks, and happy concatenating!
    0
  • Caroline Hemmer Sand
    Thank you! Super helpful. Is there  way to get the "(12 weeks)" to appear on the row under September 8, 2018? We used to add in a cell that had a shift enter in it to get this to work in workbook. 
    0
  • Mike
    No problem! And yes, there IS a way. :) I have another hack for you. Add the syntax CHAR(10) to your formula and that will add a line feed, i.e. a new row, to your result. Like so:



    Let me know if this doesn't work for you and holler if you have any further questions. Thanks, and have a super day!
    0

Please sign in to leave a comment.