Why can't dates concatenate in Spreadsheets like workbook

回答済み

デモをリクエスト

• 2018年08月28日 21:21

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

• 

  Mike

  • 2018年08月29日 23:15

  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

  • 2018年09月05日 23:20

  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

  • 2018年09月06日 00:19

  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

サインインしてコメントを残してください。