May 03 2022 01:31 PM
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,
Wm
May 03 2022 02:20 PM
Solution
I'm going to take the easy way here and point you to this page https://exceljet.net/excel-functions/excel-text-function 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.
May 03 2022 02:31 PM
May 03 2022 02:20 PM
Solution
I'm going to take the easy way here and point you to this page https://exceljet.net/excel-functions/excel-text-function 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.