Change Text/Number to valid date

tgattsh
tgattsh ✭✭✭✭✭
edited 01/26/24 in Formulas and Functions

I'm using a date in the primary column, of course Smartsheet sees this as just text/number. I've added a column called "Date" and want to convert the Text/Number to an actual date.

Primary Column...........Date

1/29/2024....................01/29/24

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The formula I provided was assuming there was only one date in the Primary Column and the month and day could be variable as in your original screenshot. Based on the new screenshot, try this in the [Starting Date] column.

    =DATE(VALUE("20" + MID([Week Of]@row, 7, 2)), VALUE(LEFT([Week Of]@row, 2)), VALUE(MID([Week Of]@row, 4, 2)))


    Making sure the [Starting Date] column is set as a date type column and using this new formula, you should be storing a date type value which in turn should allow you to use your original formula in [Friday Week Ending].

Answers

  • Charmaine
    Charmaine ✭✭
    edited 01/26/24

    @tgattsh Hi do not know if this will help.


  • tgattsh
    tgattsh ✭✭✭✭✭

    @Charmaine I very much appreciate the response. That does not help. I need a way to convert text to date within a formula.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would use something along the lines of

    =DATE(VALUE(RIGHT([Primary Column]@row, 4)), VALUE(LEFT([Primary Column]@row, FIND("/", [Primary Column]@row) - 1)), VALUE(MID([Primary Column]@row, FIND("/", [Primary Column]@row) + 1, FIND("/", [Primary Column]@row, FIND("/", [Primary Column]@row) + 1) - (FIND("/", [Primary Column]@row) + 1))))

  • tgattsh
    tgattsh ✭✭✭✭✭

    @Paul Newcome Thank you once again for your assistance. Here is better clarification of what I'm working with, please forgive my earlier example.

    "Week Of" is my primary column which as you know can only be text/number.

    "Starting Date" formula is simply =LEFT([Week Of]@row, 8).

    "Friday Week Ending" Column had a formula that is simply =[Starting Date]@row - 2. This doesn't work because it doesn't see the Starting Date as a date, only as texted and returns #INVALID OPERATION.

    I altered your suggested formula to =DATE(VALUE(RIGHT([Week Of]@row, 13)), VALUE(LEFT([Week Of]@row, FIND("/", [Week Of]@row) - 1)), VALUE(MID([Week Of]@row, FIND("/", [Week Of]@row) + 1, FIND("/", [Week Of]@row, FIND("/", [Week Of]@row) + 1) - (FIND("/", [Week Of]@row) + 1)))) and it returns an #INVALID VALUE.

    Any further suggestions would be greatly appreciated. Thank you...

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The formula I provided was assuming there was only one date in the Primary Column and the month and day could be variable as in your original screenshot. Based on the new screenshot, try this in the [Starting Date] column.

    =DATE(VALUE("20" + MID([Week Of]@row, 7, 2)), VALUE(LEFT([Week Of]@row, 2)), VALUE(MID([Week Of]@row, 4, 2)))


    Making sure the [Starting Date] column is set as a date type column and using this new formula, you should be storing a date type value which in turn should allow you to use your original formula in [Friday Week Ending].

  • tgattsh
    tgattsh ✭✭✭✭✭

    @Paul Newcome This works great Paul. You keep this up, someone's gonna write a song about you. Thank you...

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!