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 systemgenerated field.
I need the date only in another date column.
Thanks for any help!
Lori Flanigan
Best Answer

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

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 20character 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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!