Mar 15 2023 07:06 AM
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.
Mar 15 2023 07:24 AM
Mar 15 2023 07:34 AM
Mar 15 2023 07:38 AM
Mar 15 2023 07:45 AM
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")