Text to Date

Pamela Wagner

Is there an easy way to change a date that was entered into a text field and copy it as a date into a date field? I have a file that was downloaded from one system as an XLS with date and time but as a text field. I want to create a date column and somehow remove the date from the original column and put it into the date column. When I do this Smartsheet is not recognizing that the new date in the date column as an actual date. Wouldn't matter so much but I need to do a function on that column and it doesn't work since it's not seeing the new dates in the date column as actual dates.

Genevieve P.

Hey @Pamela Wagner - thanks for the tag! 🙂

If your dates are in DD/MM/YYYY then you'll need to adjust the formula to grab the Month as the middle portion, not the far left portion.

VALUE(LEFT(Resolved@row, 2)) grabs the two numbers on the far left of the cell. In the linked Community Post, their format was MM/DD/YYYY, meaning the left 2 were the month. In your date, if you have it as DD first, we'll need to change this around.

Try:

=DATE(VALUE(20 + RIGHT(Resolved@row, 2)), VALUE(MID(Resolved@row, FIND("/", Resolved@row), +1, 2)), VALUE(LEFT(Resolved@row, 2)))

The Date Function needs dates entered with YYYY / MM / DD as the format in the function but the output will be based on how you set up your Date Format in the column. Does that make sense?

Cheers,

Genevieve

Paul Newcome

This should accommodate one or two digits in both the month and day portion and allow you to not need a helper column to strip off the time piece assuming there is a space between the date and time (if there is not, we can accommodate that as well).

=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))))

Paul Newcome

@Meredith Rhodes Give this a try...

=IFERROR(DATE(VALUE(RIGHT([Verification Date]@row, 4)), VALUE(LEFT([Verification Date]@row, FIND("/", [Verification Date]@row) - 1)) + 1, 1), DATE(VALUE(RIGHT([Verification Date]@row, 4)) + 1, 1, 1)) - 1

Ipshita

Dear @Pamela Wagner I am just trying to think of certain options here so kindly bear with me even if you find them ridiculous (lol) :)

So, first of all, this file that you have downloaded from one system that is in XL format, is it an editable file? Can you add a column here next to the date column (which is a text field) and use the function DATEVALUE to convert the text field into date values? Because if you do this in the source file, it would be the easiest way out when you upload this file into Smartsheet as you can use the new date column as your date column in the sheet.

Secondly, you can use the DATE function to translate numbers into Dates, with the syntax being DATE(YYYY, MM, DD). Then we can use the RIGHT Function, MID Function, and LEFT Function to grab different elements of your text string. Here is the link to use the DATE function - https://help.smartsheet.com/function/date?_gl=1*1tnputn*_ga*NTU1OTUyNDc2LjE2OTAyODgxODM.*_ga_ZYH7XNXMZK*MTY5Njc2MzgwOS4xNS4xLjE2OTY3NjY3MzAuNDguMC4w&_ga=2.230056217.408627841.1696763807-555952476.1690288183

Also, a solution from our dear Genevieve - https://community.smartsheet.com/discussion/86372/text-to-date-field#:~:text=You%20can%20use%20the%20DATE,YYYY%2C%20MM%2C%20DD).&text=Then%20we%20can%20use%20the,elements%20of%20your%20text%20string.

Hope this helps,

Cheers!

Ipshita

Pamela Wagner

@Ipshita Mukherjee thank you for finding that guidance for me. The frustrating thing is that I HAD done something that worked that didn't involved all those formulas and in a fit of "why do I need these columns?" I deleted them and cannot for the life of me remember what I did. I'm checking to see if there is any way to get that version (I just need the one from 6 Oct) to see what I did. I have see the guidance above but just couldn't wrap my head around it knowing that whatever I did was so much easier!

Ipshita

@Pamela Wagner I hear you totally my friend. Please do let me know if you do find an easier way to achieve the solution here! Sometimes Smartsheet just plays with our minds and we overlook things that are staring straight at us!! 😄

Hope you find what you are looking for soon!

Cheers!

Ipshita

Pamela Wagner

I tried the formula @Genevieve P. listed but it's giving me an "Invalid Value" (yes the column with the formula is a date column). I want to note that I'm in the US so the date is currently: DD/MM/YYYY. I have no problem changing the format if that is what is needed. Here is the formula that I entered (with "Resolved" being the original date column:

=DATE(VALUE(20 + RIGHT(Resolved@row, 2)), VALUE(LEFT(Resolved@row, 2)), VALUE(MID(Resolved@row, FIND("/", Resolved@row), +1, 2)))

My other question is why does the date have to be YYYY,MM,DD when all of the dates that I normally put in a date column are listed in the US method?

Genevieve P.

Hey @Pamela Wagner - thanks for the tag! 🙂

If your dates are in DD/MM/YYYY then you'll need to adjust the formula to grab the Month as the middle portion, not the far left portion.

VALUE(LEFT(Resolved@row, 2)) grabs the two numbers on the far left of the cell. In the linked Community Post, their format was MM/DD/YYYY, meaning the left 2 were the month. In your date, if you have it as DD first, we'll need to change this around.

Try:

=DATE(VALUE(20 + RIGHT(Resolved@row, 2)), VALUE(MID(Resolved@row, FIND("/", Resolved@row), +1, 2)), VALUE(LEFT(Resolved@row, 2)))

The Date Function needs dates entered with YYYY / MM / DD as the format in the function but the output will be based on how you set up your Date Format in the column. Does that make sense?

Cheers,

Genevieve

Pamela Wagner

@Genevieve P. So I finally figured it out. Not sure I understand the formula which I really want to do as that would have allowed me to get to this answer sooner but I will keep working on it! There were three issues: 1) I told you the wrong format for the date, it is in the standard US format of MM/DD/YYYY; 2) the Month or Day could be either 1 or 2 characters (e.g., for May it can be 5 not 05) and 3) the original date that was exported also has a time stamp. I created a new column and pulled out just the date using LEFT and then realized that the format of MM/DD/YYYY implies that Month and Day should be 2 characters. So now I will pull that formula down (which DOES work!) and if I get an Invalid Value, sort by that and either change the date/month to be 2 characters.

What a journey! Thanks much and also to @Ipshita Mukherjee for first responding!

Ipshita

@Pamela Wagner @Genevieve P. thanks dears for the enlightening post.

@Pamela Wagner The US date format by the way is MM/DD/YYYY and not DD/MM/YYYY :) that’s why we write 9/10/2023 and not 10/9/2023

Cheers!

Ipshita

Paul Newcome

This should accommodate one or two digits in both the month and day portion and allow you to not need a helper column to strip off the time piece assuming there is a space between the date and time (if there is not, we can accommodate that as well).

=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))))

Pamela Wagner

OMG! Thank you @Paul Newcome this is a huge help and it works awesome!

Paul Newcome

Happy to help. 👍️

Meredith Rhodes

@Paul Newcome I wonder if you can help me with a text to date situation that is only the month and year in the text field (ie, 10/2023 - there is always a slash).

I've created a data shuttle from the ClinicalTrials.gov Protocol Registration and Results System that includes a record verification date, listed in a text field as MM/YYYY.

Records need to be verified annually and there is not a specific due date, but technically - it could be by the last day of the month.

I need to turn this text month and year into a due date that is essentially the next year, MM/YYYY+1. Ideally this is a date field - so we can use the last day of the month.

So text field 10/2023 to date field 10/31/2023.

Is this a possibility?

Cheers!

Meredith

Meredith Rhodes

@Paul Newcome - I'm trying to piece together a formula for converting 10/2023 (text) into 10/31/2023 (date) based on this thread and another thread you commented on here:

https://community.smartsheet.com/discussion/66735/if-submit-date-is-before-the-20th-of-current-month-set-payment-due-to-last-day-of-month

Here is where I'm at (getting incorrect argument set):

=DATE(VALUE(RIGHT([Verification Date]@row, 4), VALUE(LEFT([Verification Date]@row, FIND("/", [Verification Date]@row), 2), VALUE(LEFT([Verification Date]@row, 2, -1)))))

I read this as - look at the 4 digits on the right, make that the year, look at the 2 digits on the left (after the slash?), make that the month, and then look at the 2 digits on the left and subtract one day to get the day.... but it's not quite right. Am I close?

Meredith

Paul Newcome

@Meredith Rhodes Give this a try...

=IFERROR(DATE(VALUE(RIGHT([Verification Date]@row, 4)), VALUE(LEFT([Verification Date]@row, FIND("/", [Verification Date]@row) - 1)) + 1, 1), DATE(VALUE(RIGHT([Verification Date]@row, 4)) + 1, 1, 1)) - 1

Meredith Rhodes

Thank you @Paul Newcome! This worked!

Looks like I needed to tell it that the last value was a date too?

What do I add to this formula string to add a year to the date? Or 6 months?

Paul Newcome

@Meredith Rhodes This is actually a bit different from yours.

We do pull the RIGHT 4 for the year, but then we use a LEFT function to pull the left characters with the FIND telling us where to STOP for the month number.

Your formula has a few misplaced parenthesis, but even then we would have a few issues because it is looking for a day value.

What we did above in the working one is essentially

=IFERROR(DATE(yyyy, mm + 1, 1), DATE(yyyy + 1, 1, 1)) - 1

We use a DATE function and add 1 to the month number. Of course this will error out for December dates because there is no month 13. So we use an IFERROR to bump it to January of the following year. Both DATE functions will output the first of the next month so that when we subtract 1, we automatically have the 28th, 29th, 30th, or 31st as needed.

To add a year to the output, we would add one year to both DATE functions.

=IFERROR(DATE(yyyy + 1, mm + 1, 1), DATE(yyyy + 2, 1, 1)) - 1