How can I convert a date to plain text?

BarneeLBarneeL
edited 12/09/19 in Formulas and Functions
10/08/18 Edited 12/09/19

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?

Popular Tags:

Comments

  • Richard Rymill SBPRichard Rymill SBP Top Contributor
    edited 10/08/18

    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!

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

    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!

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    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 WilliamsJ. Craig Williams Top Contributor

    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

  • edited 04/20/20


Sign In or Register to comment.