# Extract Date from Date and Time in Date Column

✭✭✭✭✭

I have data pulled into a date column in this format: 6/1/2022 7:30:57 AM This is not a system-generated field.

I need the date only in another date column.

Thanks for any help!

Lori Flanigan

Tags:

• ✭✭✭✭✭

`=LEFT(Timestamp@row, FIND(" ", Timestamp@row))`

In your timestamp, the date is immediately followed by a space (" "). Find the position of that space and then take the leftmost characters of that position.

For example, in "6/1/2022 7:30:57 AM" the space is the 9th position. Grabbing the 9 leftmost characters returns "6/1/2022 ". Smartsheet automatically trims spaces so you're left with "6/1/2022".

`LEFT( Timestamp@row, position_of_space )` returns the date.

`FIND(" ", Timestamp@row)` returns the position of the space character.

Put together, your formula is `=LEFT(Timestamp@row, FIND(" ", Timestamp@row))`.

• Overachievers

This formula should work for you, it also allows for lesser number of characters in the date.

=IF(LEN([Text date]@row) = 19, LEFT([Text date]@row, 8), LEFT([Text date]@row, 10))

Hope that helps

Thanks

Paul

• ✭✭✭✭✭

Thanks, Paul.

That formula works for many of the rows; however, rows that have 20 characters (e.g., 6/1/2022 10:15:20 AM) include the leading 1 in the time stamp (e.g., 6/1/2022 1). I added another IF formula to your formula, but get "INCORRECT ARGUMENT" in the 20-character rows. The formula I used is: =IF(LEN([Evaluation Date]@row) = 19, LEFT([Evaluation Date]@row, 8), LEFT([Evaluation Date]@row, 10, IF(LEN([Evaluation Date]@row = 20), LEFT([Evaluation Date]@row, 8), LEFT([Evaluation Date]@row, 10)))).

• ✭✭✭✭✭

`=LEFT(Timestamp@row, FIND(" ", Timestamp@row))`

In your timestamp, the date is immediately followed by a space (" "). Find the position of that space and then take the leftmost characters of that position.

For example, in "6/1/2022 7:30:57 AM" the space is the 9th position. Grabbing the 9 leftmost characters returns "6/1/2022 ". Smartsheet automatically trims spaces so you're left with "6/1/2022".

`LEFT( Timestamp@row, position_of_space )` returns the date.

`FIND(" ", Timestamp@row)` returns the position of the space character.

Put together, your formula is `=LEFT(Timestamp@row, FIND(" ", Timestamp@row))`.

• ✭✭✭✭✭

Thank you so much! This formula works perfectly!

Lori

• ✭✭

This didn't work well for me. The output is seen as text so when you try to sort by date is sorts by the day and the months get mixed up. I found the best way was to use the formula below. Although this is not perfect as I think the day is from GMT not the actual created time in local time. But this creates data in the proper date fromat.

=DATE(YEAR(Created@row), MONTH(Created@row), DAY(Created@row))

• ✭✭✭✭

hi! what if I want to extract only the time?

• ✭✭✭✭✭✭

Hey @Xochitl C.

You can use the TIME function to convert to values recognized as time. The MID function allows you to extract from the middle of an existing textstring.

=TIME(MID(Timestamp@row, FIND(" ", Timestamp@row) + 1, LEN(Timestamp@row) - FIND(" ", Timestamp@row)))

If interested, you can find out more about the TIME function

Will this work for you?

Kelly

• ✭✭✭✭

Thanks so much @Kelly Moore , yes it works perfect! I applied this formula in the "time" column

But now I'm trying to subtract 6 hours to convert into CST time zone. I thought it might work just subtracting 360 minutes (6 hours), but I get negative numbers.

This is the formula I'm using

• ✭✭✭✭✭✭

Hey @Xochitl C.

Will this work in your cst column? It checks if the hour value in your time column begins with 6 or greater. When it doesn't the subtraction portion of the hours is reversed.

=IF(VALUE(LEFT(time@row, 2)) >= 6, TIME(TIME(time@row, 1) - 360, 0), TIME(6 - VALUE(LEFT(time@row, 2)) + ":" + MID(time@row, 4, 2), 0))

Will this work for you?

Kelly

• ✭✭✭✭

@Kelly Moore you are a GENIUS!! I have been spending many hours trying to figure out. Thank SO much!!!!!! 🙏

• ✭✭✭✭✭✭

So glad it worked for you

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!