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
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 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
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!