Change Text/Number to valid date

tgattsh

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

Paul Newcome

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

Charmaine

@tgattsh Hi do not know if this will help.

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

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

@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

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

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

Paul Newcome

Happy to help. 👍️