Extract date as date from date and time stamp

Lori Flanigan

I am importing a date and time stamp (e.g., 9/3/2022 8:36:19 AM) and need to extract the date into a date field so Smartsheet recognizes the date. I have a formula to extract the date as text (i.e., 9/3/2022) in another column, but cannot find a formula that works to convert the month and day to two digits and converts the date to the format that is recognized by Smartsheet. Your help is appreciated!

Lori

Jeff Reisman

@Lori Flanigan This is a tough one but it is possible. Assuming your field with the extracted date is called ExtractedDate.

First, you need to get the year value. That's easy: =VALUE(RIGHT(ExtractedDate@row, 4)) gets the first 4 characters starting from the right, and VALUE converts them from text to a number value.

Next, we need to get the month value. For this we need to find the first "/" from the left, and take everything to the left of it. =VALUE(LEFT(ExtractedDate@row, (FIND( "/", ExtractedDate@row) -1))) This formula uses FIND to determine the position of the first slash, and uses that value minus 1 to tell LEFT how many characters to take from the left side of the value. So for 9/3/2022, it finds the slash at position 2, so it tells LEFT to take only one character from the left side of the value.

Next we need the day value. For this we'll use FIND to locate the first and second slash, and use MID to get every thing between them. =VALUE(MID(ExtractedDate@row, (FIND("/", ExtractedDate@row) + 1), (FIND("/", ExtractedDate@row, (FIND("/", ExtractedDate@row) + 1)) - (FIND("/", ExtractedDate@row) + 1)))) - This formula uses FIND to determine the position of the first character to the right of the first slash, which is the starting point for MID to use. Then it finds the position of the second slash, and subtracts the position of the first character to the right of the first slash from it to determine how many characters MID needs to collect.

Now we use all these inside a DATE formula in a date-type field:

=DATE(VALUE(RIGHT(ExtractedDate@row, 4)), VALUE(LEFT(ExtractedDate@row, (FIND( "/", ExtractedDate@row) -1))), VALUE(=MID(ExtractedDate@row, (FIND("/", ExtractedDate@row) + 1), (FIND("/", ExtractedDate@row, (FIND("/", ExtractedDate@row) + 1)) - (FIND("/", ExtractedDate@row) + 1)))))

Khasim

Hi @Lori Flanigan

If you want to display the date in month and day format. Simply select the Entire column to change the column to short date.

I hope this helps to answer your question.

Lori Flanigan

Thanks, Khasim. I was not clear enough. I am looking for a way to change 9/3/2022 8:36:19 AM in one field to 09/03/2022 in another field that Smartsheet will recognize as a date. Currently, I have a formula that pulls 9/3/2022 into a field, but that field is read by Smartsheet as text; therefore, I cannot use that field in reports as a date field.

Jeff Reisman

@Lori Flanigan This is a tough one but it is possible. Assuming your field with the extracted date is called ExtractedDate.

First, you need to get the year value. That's easy: =VALUE(RIGHT(ExtractedDate@row, 4)) gets the first 4 characters starting from the right, and VALUE converts them from text to a number value.

Next, we need to get the month value. For this we need to find the first "/" from the left, and take everything to the left of it. =VALUE(LEFT(ExtractedDate@row, (FIND( "/", ExtractedDate@row) -1))) This formula uses FIND to determine the position of the first slash, and uses that value minus 1 to tell LEFT how many characters to take from the left side of the value. So for 9/3/2022, it finds the slash at position 2, so it tells LEFT to take only one character from the left side of the value.

Next we need the day value. For this we'll use FIND to locate the first and second slash, and use MID to get every thing between them. =VALUE(MID(ExtractedDate@row, (FIND("/", ExtractedDate@row) + 1), (FIND("/", ExtractedDate@row, (FIND("/", ExtractedDate@row) + 1)) - (FIND("/", ExtractedDate@row) + 1)))) - This formula uses FIND to determine the position of the first character to the right of the first slash, which is the starting point for MID to use. Then it finds the position of the second slash, and subtracts the position of the first character to the right of the first slash from it to determine how many characters MID needs to collect.

Now we use all these inside a DATE formula in a date-type field:

=DATE(VALUE(RIGHT(ExtractedDate@row, 4)), VALUE(LEFT(ExtractedDate@row, (FIND( "/", ExtractedDate@row) -1))), VALUE(=MID(ExtractedDate@row, (FIND("/", ExtractedDate@row) + 1), (FIND("/", ExtractedDate@row, (FIND("/", ExtractedDate@row) + 1)) - (FIND("/", ExtractedDate@row) + 1)))))