Change Text/Number to valid date
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
Best 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
-
@tgattsh Hi do not know if this will help.
-
@Charmaine I very much appreciate the response. That does not help. I need a way to convert text to date within a formula.
-
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))))
-
@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...
-
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].
-
@Paul Newcome This works great Paul. You keep this up, someone's gonna write a song about you. Thank you...
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!