Split date and time column row

Hi all,

I’m working on an excel file, i have managed to get the datetime column to be copied out onto new column. But i want the column to be separated for time and date.

Lets say if first column should contain the date only and the second column only store the time only.

i try to use split on the first column it is not working. But when i was using split on the second column it works well. Can anyone tell me what are i’m missing out here.

Attached are the output of the first column and on the second picture is the original format of the data in that row.

image

image

i do notice that there is two white spaces before the time variable. If that is what causing the error how do i overcome this.

Current datatype of this row column are in datetime format. and if i do get a solution on this i want to retain the datatype but as for the date format could be as dd-MM-yyyy in datetime format not as a string.

Please help me. Thank you in advance.

Hi,

If your datatype is date and time, you can parse it to any format you want.

DateTime.ParseExact("dd-MM-yyyy hh:mm:ss", "dd-mm-yyyy",System.Globalization.CultureInfo.CurrentCulture)

Hi,

Thank you for your quick response.
But how do i assign this in for each row activities. Given the row column name is “SLOT_PLAN_DATE”

Hi @hairil.hashimie

This might help you understand.
Use for each row and assign columns with date and time

Hi,

It is working fine but then i noticed there is some of row are not converted to the specified date format. it is still store in datetime type. did i do anything wrong cause i follow your expression exactly.

image

It will only convert where we have exact date format match, if there is even extra space in any of those, it will not convert, to check it, you can use IF with datetime.tryparseexact.

Noted on that and by fact that was my concern on this thread. i was actually want to trim of two white space from that specific string. Have tried one using regex but seems like its not working. Currently still searching an option on how to remove 2 white spaces cause as for now the only i found just have 1 white space only.

Hi,

I have read your answer on that thread. May i know as i’m still new in this. For that specific condition what it actually do yeah? Hope you don’t mind to explain so that i could get a better and more clear picture about uipath.

@hairil.hashimie
It depends on in and out use of particular object, but to keep it simple.
I would have used tryparseexact as it will also control my flow and avoid any unknown exception in my flow.

Also, if you can share a dummy file, I could have looked at it and told you a better approach.

@hairil.hashimie
find some starte help:

dateTimes can be parsed by the strings, but its internal format is to keep and not to reformat.
Alternate value representations are created on String Base

book1.xlsx (39.1 KB)

Hi Lakshay,
I have shared you a dummy file that is used in current thread. Thanks in advance for your help on this.
On the replies below does it mean i could just parse any of this? i got query lets say if you just wanna parse the date only why should we bother about the time format also? Does it mean we need bot of it before we are parsing it into a new format? Being stuck on this for such time understand the concept of parsing this date time format. sorry for mess and hope you dont mind im asking this.

Hi @Lakshay_Verma aew you able tosolve it?

Hi @Lakshay_Verma i want to ask if i ahve bank sheet and in accout column i have to take only 8 digit account number and i have to tally it with other column with account 045 and have to write in other sheet pls help if necessary i acn provide you screenshot

Hi @akritiverma316

Please create a new topic, It will help others as well.
Thanks

@hairil.hashimie I 'll check and respond

Thank you for your replies. I have also tried regex, split, ltrim and convert the date into string first before applying the split method but none are working. Kinda stuck at here on how to actually trim or change that date format. But for awhile ill still find a way to solve it.

@hairil.hashimie

Issue was not with your data but there are empty rows after 1544 or 1545 which was causing this issue.
Just created a flow. Please have a look. Case1.xaml (7.5 KB)

hi Lakshay,

Thank you so much for your help. at first it doesn’t work but then i made some changes on the workflow and then it works. But got confused why is it this two date time are being left out? GGot any clues why?

image

@hairil.hashimie hat was not left out, it was already there, try to put this into a new sheet and you will there was nothing left. And the reason it was there because code has removed two spaces in datatable that was in your Excel file.

If this helps you please mark it as answer and close this topic.