Text to Date

Meredith Rhodes

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

Paul Newcome

@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

Meredith Rhodes

Thank you for all of your help with this @Paul Newcome 😊

Paul Newcome

@Meredith Rhodes Happy to help. 👍️

Pamela Wagner

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.

Genevieve P.

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

Paul Newcome

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

Pamela Wagner

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.

Paul Newcome

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.

Pamela Wagner

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.

Paul Newcome

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