Text to Date
Answers
-
Thanks again @Paul Newcome
When I use this formula to reference the text Verification Date 01/2023
=IFERROR(DATE(VALUE(RIGHT([Verification Date]@row, 4) + 1), VALUE(LEFT([Verification Date]@row, FIND("/", [Verification Date]@row) - 1)) + 1, 1), DATE(VALUE(RIGHT([Verification Date]@row, 4)) + 2, 1, 1)) - 1
I turn text Verification Date 01/2023 into Verification Due Date 12/31/24
I'd like to turn 01/2023 (text) into 01/31/2024 (due date). I think I need to add 13 months and subtract a day?
I'm trying all sorts of permutations - perhaps my formula isn't quite right.
Weirdly, when I convert this to a column formula - it is turning all of the dates to 12/31/24 rather than looking at the respective [Verfication Date]@row. Is this due to the IFERROR ?
Thanks again for your help.
Meredith
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
-
@Meredith Rhodes It is the IFFERROR kicking in even on the January one because of a misplaced parenthesis.
The closing parenthesis after the +1 should be moved to before the +1.
As it is you have
VALUE(RIGHT() + 1)
You need
VALUE(RIGHT()) + 1
-
Thank you for all of your help with this @Paul Newcome 😊
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
-
-
I want to thank everyone who offered help on this question (@Genevieve P. @Paul Newcome) and I wanted to come back and state that I did find an easier way but it was doing it in XLS first. I am going through a training on XLS formulas so I could understand how to use formulas in Smartsheet easier and found a shorter way to do it in XLS then import the data into Smartsheet. Now I will be able to tackle the formula that you all helped with in Smartsheet so that I can understand the syntax. One question I cannot resolve is the reference to "!" in the formula:
=DATE(VALUE(20 + MID(Resolved@row, FIND(" ", Resolved@row) - 2, 2)), VALUE(LEFT(Resolved@row, FIND("/", Resolved@row) - 1)), VALUE(MID(Resolved@row, FIND("/", Resolved@row) + 1, FIND("!", SUBSTITUTE(Resolved@row, "/", "!", 2)) - (FIND("/", Resolved@row) + 1))))
What is that doing there? I can't find a reference for it when I google it for either XLS or Smartsheet.
-
Hey @Pamela Wagner
I think Paul can answer this one better than me since I see it in his formula earlier in this thread!
My guess is that we're using "!" as a unique value to search for by replacing one of the / marks with !. Otherwise you would have two / in the same field so it would be difficult to find the correct one
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
@Pamela Wagner It is essentially our way of locating the second "/". We use the SUBSTITUTE function to replace the second "/" with the exclamation point then FIND that.
SUBSTITUTE(Resolved@row, "/", "!", 2)
FIND("!", SUBSTITUTE(.....))
The second "/" minus one character to the right of the first "/" gives us a dynamic one or two digits in between the two slashes.
FIND("!", SUBSTITUTE(.....)) - (FIND("/", Resolved@row) + 1)
This number tells the MID function how many characters to pull from after the first "/". Wrapping that in the VALUE function then gives us a numeric output for the days in a string that is in mm/dd/yy format but where "dd" could be either one or two digits.
It is a similar concept to how we used the FIND function to locate the first "/", subtract one from that number, and use that to tell us how many characters from the LEFT to pull to generate the month that could be either one or two digits.
-
Thanks @Paul Newcome! Just so that I can complete the circle, here is the XLS formula that seems to work well:
=LEFT(F4,SEARCH(" ",F4)-1)
Not sure of the syntax if this will work in Smartsheet or not.
-
That looks like it is pulling the date from a date/time stamp or some other type of string where the date comes first.
The direct replica of that formula in Smartsheet would simply be a FIND in place of the SEARCH function, but the LEFT function outputs a text string which won't be usable as a date.
You wouldn't be able to sort of filter on it as you would expect to. That's why we just use the DATE function and strip each piece out individually.
-
I think that is less an issue for me than the long formula. When I was playing around with it, I had imported dates but the column was being seen as text so I just changed the column properties to Date and it worked fine. I think that the solution depends on what you are comfortable with.
-
@Pamela Wagner That is true of importing from Excel as Excel will store and pass it as a date. Working strictly in Smartsheet though would require the use of the longer formula regardless of column type manipulation.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Trending in Smartsheet Basics
- Can I link a Smartsheet report in a form. My form will be for a person to confirm info from a report is correct. So I want the user to be able to view the report while filling out the form.
- I have 4 sheets used by individuals to track their work. I have a report that pulls from all 4 of those sheets to give me a total count of items they listed for the month as well as each line item (de…
- Hi All! Quick question - I am working on a form with a lot of yes or no questions, and I decided to display them as horizontal radio buttons. They are all circles except for one question, which shows …
