CONCATENATE - copy cell values ?

New Contributor

I'm having trouble trying to piece together data. I have columns which are formatted as dates and times. (Cells are set to format data as displayed below.)

|  G9   |     H9     |      I9      |    J9    |

| Day | ShortDate | StartTime | EndTime |

| Thursday | 6/16 | 6:00 PM | 8:00 PM | 


I'm trying to create one concatenated cell with all of the data. (This one cell would be copied over to another sheet, where I'm building a table to embed in a Word document.)

=CONCATENATE(G9&" "&H9&" from "&I9&" to "&J9) 

That "should" read "Thursday 6/16 from 6:00 PM to 8:00 PM" 

However, I'm getting this:

Thursday 44728 from 0.75 to 0.8125


I assume that this is an issue with the cell being formatted but the value being carried over to the concatenation..? Is there a (correct or proper) way to do this? I've tried adding VALUE(H9) and similar to the formula but that doesn't work.

Thanks in advance,



2 Replies
best response confirmed by MortonVisuals (New Contributor)



I'm going to take the easy way here and point you to this page where you'll find the answer to your question. When concatenating fields that are in fact numbers, you need to convert them to text, and there's a definite syntax for that. That page will introduce you to the correct syntax for each of those numeric fields.

Thanks! I stumbled across a similar page a short while ago and realized that I didn't have my text "inserts" within the concatenate formula formatted quite right. I've got it working now, as wanted. Thank you for your time!!