Concatenating cells including date cell

Copper Contributor

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.

Entered textStatic valueEntered textEnd result


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.


4 Replies


This is untested.



Thank you, that gives the result of 2019-01-01T0. Maybe the issue is more within the time column of 0:00:00.
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?


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.