How can I convert a date to plain text?
I would like to be able to use a formula to generate a date in a text field.
However, when I try the formula, for example
=TODAY() + 1
I get the error #INVALID COLUMN VALUE
Is there a way I can convert this to text so I can I have it in a text/number column?
Comments
-
That's one for my colleague @Craig Williams he loves questions like that?
CW
have you got the answer to that one?
RichardR
-
Sounds like it's not going to be as simple as you might hope then!
-
This worked for me.
=MONTH(Date) + "/" + DAY(Date) + "/" + YEAR(Date)
To add a day
=MONTH(Date) + "/" + DAY(Date+1) + "/" + YEAR(Date)
You can do the same for month and year if needed.
-
Hi BarneeL,
All you need to do is to change column properties to date one. It has to be this way, because you want to count some dates. Otherwise you have such error.
BR
Marcin
-
This is certainly the best solution. Thank you!
-
Hey Marcin,
What I am using the dates for is purely as a series of headers for columns. Unfortunately the row type needs to be either dropdown or text/number, and won't work with the 'date' column type. Hence my question.
See Marcus's comment - he has come up with a solution that has worked for this purpose!
-
Here's the trick:
=(-your-date-formula) + ""
=TODAY(1) + ""
will be 10/10/2018 (as I type this).
The + "" will convert the formula in the parenthesis () to text.
Craig
-
Even if there wasn't an easier solution (see below), this
DAY(DATE + 1)
is not correct.
On the last day of the month, it will shift to the first day of the month, but this is not accounted for in MONTH(DATE)
There is a similar problem at year's end.
Craig
-
Great! Thank you. Did not know it could be this simple
-
@J. Craig Williams I tried to figure out forever how to convert date cell into a text cell. This worked! THANK YOU!
-
This works for me
Turns any date into DD MMMMM YYYY
=DAY(([Start Date]@row)) + " " + IF(MONTH([Start Date]@row) = 1, "January") + IF(MONTH([Start Date]@row) = 2, "February") + IF(MONTH([Start Date]@row) = 3, "March") + IF(MONTH([Start Date]@row) = 4, "April") + IF(MONTH([Start Date]@row) = 5, "May") + IF(MONTH([Start Date]@row) = 6, "June") + IF(MONTH([Start Date]@row) = 7, "July") + IF(MONTH([Start Date]@row) = 8, "August") + IF(MONTH([Start Date]@row) = 9, "September") + IF(MONTH([Start Date]@row) = 10, "October") + IF(MONTH([Start Date]@row) = 11, "November") + IF(MONTH([Start Date]@row) = 12, "December") + " " + YEAR([Start Date]@row)
-
All of these certainly work and I will definitely be using at least one of them, but my greater question is this: Why aren't dates allowed in a Text/Num column?
I can see us not being allowed to use them for date-related calculations [it would throw up an #INVALID DATA TYPE error on attempted use], but it should be able to be easily treated as a string because, ultimately, it is one.
I've had to jump through some convoluted hoops with this before and it's a near-constant headache, made all the worse by the fact that I can't see a reason for it. Can somebody explain?
-
You can use dates and date formulas as much as you want in any column type. In fact there is no formula that is restricted to a column type. What is restricted is what the formula outputs. If you want a date in a text column, just add text to it, ( + "").
This is fairly standard for spreadsheet/database software and coding languages as it allows them to better optimize the data storage. Also, it isn't a string, dates are represented by integers that are then converted to dates, you don't see the integers as they are already converted for you.
-
Thank you Daniella,
I have put together the string you show and it functions, however there is a 0 in front of the month(text). I am unsure on how to stop this. Any suggestions would be great.
=IF(MONTH(Created@row) = 1, "January") + IF(MONTH(Created@row) = 2, "February") + IF(MONTH(Created@row) = 3, "March") + IF(MONTH(Created@row) = 4, "April") + IF(MONTH(Created@row) = 5, "May") + IF(MONTH(Created@row) = 6, "June") + IF(MONTH(Created@row) = 7, "July") + IF(MONTH(Created@row) = 8, "August") + IF(MONTH(Created@row) = 9, "September") + IF(MONTH(Created@row) = 10, "October") + IF(MONTH(Created@row) = 11, "November") + IF(MONTH(Created@row) = 12, "December")
-
I have a master data sheet for branch details. I use Vlookup to create task sheets for each branch. My formula is =VLOOKUP(Notes6, {Integration}, 7, 0). The columns I have on the master sheet are date columns. The column I am pulling into is a text column. When they pull into my branch sheet I get #Invalid Column Value. What do I add in my formula to allow the date to come into a text column?
Help Article Resources
Categories
Check out the Formula Handbook template!
