Extract Date from Date and Time in Date Column

Lori Flanigan · 08/04/22

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

Toufong Vang · 08/08/22

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

Paul McGuinness · 08/04/22

Hi @Lori Flanigan

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

Lori Flanigan · 08/04/22

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

Toufong Vang · 08/08/22

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

Lori Flanigan · 08/09/22

Thank you so much! This formula works perfectly!

Lori

StuartE · 11/09/23

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

Xochitl C. · 03/03/24

https://community.smartsheet.com/discussion/comment/338950#Comment_338950

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

Kelly Moore · 03/03/24

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

https://help.smartsheet.com/function/time

Will this work for you?

Kelly

Xochitl C. · 03/03/24

https://community.smartsheet.com/discussion/comment/423004#Comment_423004

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

Kelly Moore · 03/03/24

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

Xochitl C. · 03/03/24

https://community.smartsheet.com/discussion/comment/423006#Comment_423006

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

Kelly Moore · 03/03/24

So glad it worked for you

Extract Date from Date and Time in Date Column

Best Answer

Answers

Help Article Resources

Categories