Conversion of date format to only ("dd.mm.yyyy") only the excel column

I’m trying to get uipath to read the date format in dd.mm.yyyy for data entry in SAP for the attached testdate.xlsx (8.9 KB) . However, the data extracted and written into the report becomes any other date format whether it is dd/mm/yyyy or mm/dd/yyyy. May I know how to convert all dates no matter what format in the workbook into dd.mm.yyyy only? I tried the replace("/",".") but obviously it doesn’t work as it isn’t string.

Thanks so much in advance

2 Likes

to convert this format of date 05/02/2019 00:00:00 to dd.MM.yyyy
kindly try like this buddy
where once after reading the excel with read range and for each row loop we assign the data value obtained from the datatable to a variable of type string name in_date_string
in_date_string = "05/02/2019 00:00:00"
then
to convert to date format we need
out_date_string = Datetime.ParseExact(in_date_string ,“dd/MM/yyyy hh:mm:ss”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd.MM.yyyy”)

Cheers @junnieset

3 Likes

Hi @junnieset ,

I have created a workflow base on your requirement.

Datetime.ParseExact(in_date_string ,“MM/dd/yyyy hh:mm:ss”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd.MM.yyyy”)

DateFormat.zip (12.2 KB)

Thanks,
Mohanraj.S

1 Like

This is working. Can I ask if I can do a write range activity to write the result back to replace the cells?

This is working. Can I ask if I can do a write range activity to write the result back to replace the cells?

1 Like

@Palaniyappan Hi, after that can you advise me how to write the out_date_string to replace the cells? Thanks a lot.

1 Like

Yah sure… @junnieset

@Palaniyappan how to write the result back in to overwrite the dates? What range should I indicate in the row?
image

1 Like

Can i have a full view on input value been passed to write cell…buddy… @junnieset
And where you are using this write cell activity
Inside a for each loop or a for each row loop
Cheers @junnieset

Fine…to mention the range in write cell activity kindly proceed with the below steps

  1. Use a excel application scope and pass the file path as input to this activity
  2. Use a read range activity and get the output of the variable or type datatable…named outdt…hope you would have got these details already
  3. Now use a while loop and pass the above.obtained variable as input like this
    Counter > Outdt.rows.count
    Mean while create a variable named counter of type integer…with a default value 2
  4. Inside the while loop use a write cell activity and if you want to enter the value in E column (you can mention any column you want ) mention as this in range value
    “E” + Counter.ToString
  5. Below this write cell activity use a assign activity and mention like this to increment the counter
    Counter = Counter +1
    All these activities inside a excel application scope activity buddy
    Cheers @junnieset

@Palaniyappan This is my workflow. I probably have not passed the date format properly. datetest.xaml (9.7 KB)

1 Like

here you go
hope this would work for you
jun.zip (2.7 KB)
cheers @

@Palaniyappan I think some variables are not passed and nothing had changed in the excel. Can I also check, what if the date format can be dynamic like dd/MM/yyyy, MM/dd/yy, etc. How can I just get Uipath to handle all of them and convert them to dd.MM.yyyy?

Yes of course…
May i know what format you expect to be in…
Cheers @junnieset

@Palaniyappan People can send me in any date format but I will need to convert all formats to dd.MM.yyyy. When I see Datetime.ParseExact(in_date_string ,“MM/dd/yyyy hh:mm:ss”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd.MM.yyyy”) it gets tricky. As the format I receive might not even be “MM/dd/yyyy hh:mm:ss”. So is there any way to script it dynamically? Any kind of date format to dd.MM.yyyy

@junnieset
hi junnieset,
i’m also facing the same problem.
did you find any solution for this?

Hi, I tried ".tostring.replace(“dd.MM.yyyy”,“MM.dd.yyyy”) " in a way to interchange the month to day. It’s not the correct way but it solves my issue for now. @SAI_PRASAD

i’m also using the same solution as you do.
but it is only possible when user gives dates in a particular format and if dates are given in different format then there pops up an error.
so, if you find the solution in future please share.
Thanks in advance. @junnieset