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

Help
1

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
2

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

4 Likes
3

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
4

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

5

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
6

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

1 Like
7

Yah sure… @junnieset

8

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

1 Like
9

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

10

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
11

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

1 Like
12

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

13

@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?

14

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

15

@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

16

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

17

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

18

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