Hi it possible to show how to separate the date and time.
Last Seen Date2022-08-17 13:15:46GMT2022-08-17 14:50:01GMT2022-08-17 06:30:06GMT
Last Seen Date - Zscaler Time2022-08-17 13:15:46GMT2022-08-17 14:50:01GMT2022-08-17 06:30:06GMT
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:
Hi @rmartinez4
An alternative way is to use Regex for the time:
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
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.
One way of doing it using Regex Tool, with this expression and parsing as the output:
(\d+\s[[:alpha:]]+\s\d{4})\s(\d{2}\:\d{2}\u{2})
Hi @rmartinez4 , I'd recommend the Text To Columns tool to split on the space between date and time
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.
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 space2022-08-1714:50:01GMT No space2022-08-1706:30:06GMT No space
Hi Phil,
Since I have your attention. : )
Will you help me separate this 24Jun202202:52AMGMT-0400 with this format.
Thank You,
Not needed. Thank you for all your help.
By any chance can you assist me separating this Date time 24 Jun 2022 02:51AM GMT-0400. I don't required the GMT-0400 information.
Thank You
