String to date and time

[Dalmatino16]

Hi, I have a problem parsing string using string to date and time node. The thing is that its a long list of dates and some are formated as 01/28 while others have just 1/28 with blank infront of the month if its the single digit. I have used this

and it works for the 01/28 format, but not for the other. How to write the date format in the node to be universal? Any ideas please.

[bruno29a]

Hi @Dalmatino16

M and d are for single digit representation of Month and day respectively. For 2 digits, you need to use MM and dd.

As you can see in your data, your month and day are formatted in 2 digits (01 vs 1)

Therefore, the correct format to use here is:
HH:mm yyyy/MM/dd

If you have multiple formatting in the same column, then you may need to standardize the data.

You can extract the Month and Date, and then use the padLeft() function on them and then re-concatenate them, all via the String Manipulation node.

EDIT: Here’s a quick workflow I put together for you:

Input:

Results:

That’s how the manipulation was done:

join($column1_Arr[0]$
   , " ", string($column1_Arr[1]_Arr[0]$)
   , "/", padLeft(string($column1_Arr[1]_Arr[1]$), 2, "0")
   , "/", padLeft(string($column1_Arr[1]_Arr[2]$), 2, "0")
)

Here’s the workflow: String to datetime mix of single and double digits.knwf (14.2 KB)

[Dalmatino16]

Thanks Bruno, but what I have is not a mix of single digit and double digit, but mix of “empty space single digit” and double digits. Note the empty space

[berserkersap]

Hello Dalmatino16,
I think you need to select these options in Cell Splitter (Separate date into Year, Month, Day)

[bruno29a]

Hi @berserkersap , you have to factor in the space between time and date.
FYI, my Cell Splitter is already using the options that you suggested.

@Dalmatino16 it would help if you could show us the data so we can understand what pattern is there. I mean, based on what you had given, it only showed the issue of single digit vs double digits. There was no indication about having spaces within the dates.

As I always say, “help us help you” by giving us as much details as possible. The more info we get, the more precise the solution will be.

EDIT: I modified the first Cell Splitter that separates the Time and Date so that it does not split on the other spaces in the date:

That’s the only change needed in my workflow. It will work with whatever I had as example, + with the space that you mentioned

Input:

Results:

Here’s the updated workflow: String to datetime mix of single and double digits.knwf (14.2 KB)