How can I convert a date to plain text?

BarneeL

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?

Find more posts tagged with

Formulas

Comments

Richard Rymill SBP

That's one for my colleague @Craig Williams he loves questions like that?

have you got the answer to that one?

RichardR

BarneeL

Sounds like it's not going to be as simple as you might hope then!

Marcus Odum

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.

Marcin P

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.

Marcin

BarneeL

This is certainly the best solution. Thank you!

BarneeL

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!

J. Craig Williams

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

J. Craig Williams

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

BarneeL

Great! Thank you. Did not know it could be this simple

Anya Brudzinska

@J. Craig Williams I tried to figure out forever how to convert date cell into a text cell. This worked! THANK YOU!

Daniella Said

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)

Nik Fuentes

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?

L_123

@Nik Fuentes

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.

TDobson

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.

0Month.pdf

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

RehmRick

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?

Sign in to join the conversation!

Quick Links

Trending discussions

Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…
count if value is between a date range (reference between 2 sheets)
Hi team i have 2 sheets for material inventory. the first is to record witch equipment is ship out for repair and the other is the total of equipment available. the first is set as follow: equipment type, date out, date return, quantity (negative number) the other one is as follow: each row is for a single date with the…