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.
Example:

Entered textStatic valueEntered textEnd result
01/01/2019T00:00:002019-01-01T00:00:00
02/01/2019T00:00:002019-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.

 

4 Replies

@ddelise 

This is untested.

=TEXT(A2,"YYYY-MM-DD")&C2&D2

 

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?

@ddelise 

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")