How can I convert a date to plain text?

BarneeL ✭✭
edited 01/17/24 in Formulas and Functions

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


Is there a way I can convert this to text so I can I have it in a text/number column?



  • Richard Rymill SBP
    Richard Rymill SBP ✭✭✭✭✭✭
    edited 10/08/18

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


    have you got the answer to that one? 


  • BarneeL
    BarneeL ✭✭

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

  • Marcus Odum
    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.

  • 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.



  • BarneeL
    BarneeL ✭✭

    This is certainly the best solution. Thank you!

  • BarneeL
    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
    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.



  • J. Craig Williams
    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.


  • BarneeL
    BarneeL ✭✭

    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!

  • Daniella Said
    edited 07/29/21

    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
    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
    L_123 ✭✭✭✭✭✭
    edited 08/12/21

    @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
    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.

    =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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!