# Extract date as date from date and time stamp

Options
✭✭✭✭✭
edited 08/17/22

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

Tags:

• ✭✭✭✭✭✭
Options

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

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭
Options

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

Thanks & Regards

Khasim

SSPM Consultants

Email ID: info@sspmconsultants.com

Did I answer to your question or fix the problem? Please `help` the `Smartsheet Community` by voting it Insightful/Vote Up/Awesome, or/and Accepted Answer. It will make it easy for others to discover a solution or help in answering!

• ✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!