Extract date as date from date and time stamp

Options
Lori Flanigan
Lori Flanigan ✭✭✭✭✭
edited 08/17/22 in Formulas and Functions

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:

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 08/18/22 Answer ✓
    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

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

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

Answers

  • Khasim
    Khasim ✭✭✭✭✭
    Options

    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.



    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!

  • Lori Flanigan
    Lori Flanigan ✭✭✭✭✭
    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.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 08/18/22 Answer ✓
    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

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    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!