Forum Discussion
Concatenating cells including date cell
Hi, I need a formula that copies an entered date into a specific format that can then be turned into text-field for further calculations. I've found if it's not a text field, whenever I reference it in further calculations it still turns into a date # like 43466 or something similar.
Example:
| Entered text | Static value | Entered text | End result |
| 01/01/2019 | T | 00:00:00 | 2019-01-01T00:00:00 |
| 02/01/2019 | T | 00:00:00 | 2019-02-01T00:00:00 |
The 'Static value" column will always display a static value of a single letter.
My closest attempts has been been:
Adding a convert-to-text column for cell B1, then combining the (right) and (left) and (mid) parts of the cell. Excel throws this as an #VALUE error, and is still left in the date format
Adding a convert-to-text cell for cell B1, then concatenating all the cells. But Excel still recognizes this cell as a date and the end result is 2019-01-01T0.
Thanks in advance.
- I have been able to use another cell to text cell to get my result of 2019-01-01T00:00:00. However this is stuck in the date format. Is there a way to turn this into text without providing a number format, since none exists for this format?
- Thank you, that gives the result of 2019-01-01T0. Maybe the issue is more within the time column of 0:00:00.
The last bit can't be 0 because the header of the column says "Entered text".
So it should produce 00:00:00.
If your intention was to mislead the helpers and it's really a time value then use the TEXT() function.
TEXT(C2,"hh:mm:ss")
