Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Text to Date Field

    ✭✭
    in Formulas and Functions

    Hi,

    I've been looking at the community board for a formula to convert a text date (ie. 05/21/21) to a DATE type data entry in a new column. Any suggestions?

    Thanks,

    John

    Β· Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • Employee
      Answer βœ“

      Hi @John Stanik

      Will your text entry always have the exact same format of mm/dd/yy?

      You can use the DATE function to translate numbers into Dates, with the syntax being DATE(YYYY, MM, DD).

      In this instance, we'll want to use the VALUE Function around each number set we're grabbing, like so:

      DATE(VALUE(20 + YY), VALUE(MM), VALUE(DD))

      Then we can use the RIGHT Function, MID Function, and LEFT Function to grab different elements of your text string.

      DATE(VALUE(20 + RIGHT(YY)), VALUE(LEFT(MM)), VALUE(MID(DD)))

      For the MID portion, we'll need to use a FIND Function to find the starting position (a number) of whatever it is looking for, in this case a "/". The formula uses the number it finds as a starting position in the text string.

      Full Formula:

      =DATE(VALUE(20 + RIGHT([Text Column]@row, 2)), VALUE(LEFT([Text Column]@row, 2)), VALUE(MID([Text Column]@row, FIND("/", [Text Column]@row) + 1, 2)))


      You'll need to enter the formula into a Date Column. Let me know if this works and makes sense!

      Cheers,

      Genevieve

      Need more information? πŸ‘€ | Help and Learning Center

      こんにけは (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!πŸ‘‹ | Global Discussions

      Β· Share on FacebookShare on Twitter

    Answers

    • Employee
      Answer βœ“

      Hi @John Stanik

      Will your text entry always have the exact same format of mm/dd/yy?

      You can use the DATE function to translate numbers into Dates, with the syntax being DATE(YYYY, MM, DD).

      In this instance, we'll want to use the VALUE Function around each number set we're grabbing, like so:

      DATE(VALUE(20 + YY), VALUE(MM), VALUE(DD))

      Then we can use the RIGHT Function, MID Function, and LEFT Function to grab different elements of your text string.

      DATE(VALUE(20 + RIGHT(YY)), VALUE(LEFT(MM)), VALUE(MID(DD)))

      For the MID portion, we'll need to use a FIND Function to find the starting position (a number) of whatever it is looking for, in this case a "/". The formula uses the number it finds as a starting position in the text string.

      Full Formula:

      =DATE(VALUE(20 + RIGHT([Text Column]@row, 2)), VALUE(LEFT([Text Column]@row, 2)), VALUE(MID([Text Column]@row, FIND("/", [Text Column]@row) + 1, 2)))


      You'll need to enter the formula into a Date Column. Let me know if this works and makes sense!

      Cheers,

      Genevieve

      Need more information? πŸ‘€ | Help and Learning Center

      こんにけは (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!πŸ‘‹ | Global Discussions

      Β· Share on FacebookShare on Twitter
    • ✭✭

      Thank you so much @Genevieve P. , that worked. I appreciate the detailed breakdown of how it works! That totally helps me understand the logic.

      Best,

      John

      Β· Share on FacebookShare on Twitter
    • ✭✭✭✭
      edited 10/14/22

      @Genevieve P. I'm trying to use the formula that you suggested but am getting #Invalid Value. Here is the value I'm attempting to pull and the formula modified from what you suggested above:


      Formula: =DATE(VALUE(20 + RIGHT([FY22 W46]2, 2)), VALUE(LEFT([FY22 W46]2, 2)), VALUE(MID([FY22 W46]2, FIND("/", [FY22 W46]2) + 1, 2)))

      Text Field: 08/14/22


      Edit: I think I figured out the issue - the column is a text column. Is it possible to make a single cell at the very top a date cell? I need to do some formulas to show if the date is equal to today's date.

      Β· Share on FacebookShare on Twitter
    • Employee

      Hi @Sarah123

      The DATE function above is what we're using to turn a text cell into a Date. You can compare this to Today's Date using the TODAY function!

      For example:

      =IF(DATE(formula) = TODAY(), value if true, value if false)

      Does that help?

      Need more information? πŸ‘€ | Help and Learning Center

      こんにけは (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!πŸ‘‹ | Global Discussions

      Β· Share on FacebookShare on Twitter
    • ✭✭✭✭

      Oh, in my case it may be much harder..

      image.png


      Β· Share on FacebookShare on Twitter
    • Employee

      Hi @Volodymyr L

      It looks like you've been working through this on another thread, here, and Debbie has some great suggestions for you!

      Need more information? πŸ‘€ | Help and Learning Center

      こんにけは (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!πŸ‘‹ | Global Discussions

      Β· Share on FacebookShare on Twitter
    • ✭✭✭✭

      Thank you very much Smartsheet team for your awesome programm!!!!!!!!!!

      Yup, i`m glad with here help

      Β· Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions