How to separate date and time

[rmartinez4]

Hi it possible to show how to separate the date and time.

Last Seen Date
2022-08-17 13:15:46GMT
2022-08-17 14:50:01GMT
2022-08-17 06:30:06GMT

Last Seen Date - Zscaler      Time
2022-08-17                          13:15:46GMT
2022-08-17                          14:50:01GMT
2022-08-17                          06:30:06GMT

[michelle_mathews]

Hi @rmartinez4 , I'd recommend the Text To Columns tool to split on the space between date and time

[Maskell_Rascal]

Hi @rmartinez4 

If you're not looking to convert the date or time into any specific format, you can use a Text to Columns tool configured like so. 

Let me know if that works for you. 

Cheers!

Phil

[rmartinez4]

The issue is that I don't have a space between the time and date to use the text to columns.

2022-08-1713:15:46GMT No space
2022-08-1714:50:01GMT No space
2022-08-1706:30:06GMT No space

[DataNath · Accepted solution]

If your data is a consistent structure then you could just use Substring() functions based on the position of where the 2 elements will split:

[DavidSkaife · Accepted solution]

Hi @rmartinez4 

An alternative way is to use Regex for the time:

[Maskell_Rascal · Accepted solution]

Hi @rmartinez4 

Thank you for the additional information. If there is no space between Date and Time, you can still convert the Date/Time to a Date since its the beginning of the field it will strip out the remaining when converting. You can then use something like Substring or Right functions to get the remaining part split. 

Here is an example of where I did it with formulas and converted them at the same time. 

If you need the GMT portion of it in Time, just omit the DateTimeParse part of the formula. 

Cheers!

Phil

[rmartinez4]

Hi Phil,

Since I have your attention. : )

Will you help me separate this 24Jun202202:52AMGMT-0400 with this format.

Thank You,

[Maskell_Rascal · Accepted solution]

@rmartinez4 

Is the -0400 important in the conversion? Typically that is used to show the time offset from GMT. If that's needed, there is most likely some more we can do to account for it. If its not needed, then the below formula will work. 

DateTimeParse([Field1],'%d%b%Y%I:%M%p')

This turns that date/time into the following:

While this is in just one field now, you can use some additional parsing to get them into separate fields if needed. Something like the below:

If there are continued inconsistencies in your date/time fields, you can write some logic to catch them and apply each desired format/parsing. 

Hope this helps.

Cheers!

Phil

[rmartinez4]

Not needed. Thank you for all your help.