Date format change in excel column

Help Activities
1

Hi friends,

I have date column(column T) in my excel as shown in below format

image
image409×560 64.8 KB

image

I want to change the whole column values into “dd/MM/yyyy” format…

May I know is there any shortcut to do it without looping each column?Because when I tried to change it in the Format cells option, the column values are not turning into the required format(“dd/MM/yyyy”).

Please suggest me some idea on to how to achieve this. @Palaniyappan

Thanks in advance!

2

Hi @eshwarsai_ks

You can do it by using Convert.ToDateTime(row( “cancelled at”).ToString(“dd/mm/yyyy”))

Without looping u can’t parse the date

Thanks
Ashwin.S

1 Like
3

oh cool,Thanks for your quick response @AshwinS2 :+1: I will try and get back to you . Because the rows in the excel will be more than 5k. So I worry about how to reduce the time on it @AshwinS2

4

So, I need to get the last row count of the column and then I need to take that value and loop through with the format . After that I should use the write cell activity to fill the values?

Is there any other way to fill the entire column other than the above @AshwinS2

5

Hi @eshwarsai_ks,

A simple approach is by using Invoke VBA activity of uipath and in that activity use a vb script on the column you want to change the date format , this would hardly take 5 seconds to run in full.

Cheers!

1 Like
6

@eshwarsai_ks
give a try on balarevas (Easy) Excel Component and provided activities:
grafik

getting last row index:
grafik

reformatting the cells.
grafik

1 Like
7

HI @ppr, I have tried this approach. But, it did not work. the format is not changing. Even if i tried manual also, it is not changing yar .

Thank you for your quick reply :slight_smile:

8

Will try this step and get back to you @Shikhar_Tandon. Thank you so much :slight_smile:

9

@eshwarsai_ks
just share your implementation with us

  • which format string was applied?
  • which range was used
  • was the excel also been closed during execution
    if possible share the excel with us as well

thanks

1 Like
10

image
image1417×359 20.4 KB
swaps to date.xlsx (957.6 KB)

Please find the screenshot and the excel sheet for your reference @ppr

I need to change for the columns from N to T column in the excel. I have tried it for just N column…it was not working.

11

@eshwarsai_ks
i would set the doubts on the format

  • just check if the format string would work when doing it manually in excel
    • as the format string are dependend to locals, just tell us on which languguage / locals you do operate

However with doing it directly on the source data the solution approach has its charm. So let’s take @balupad14 into the round as well

12

As I have mentioned that I am trying to change it to “dd/MM/yyyy hh:mm” format. it is not happening while doing it manually @ppr

Using Read range and foreach approach , it is working,but it is taking lot of time to finish it. it is converting each row into dateformat and doing it which will take lot of time. as the excel sheet may have more than 5k rows

13

Will you pls share the code sample @Shikhar_Tandon

14

Sure, just wanted to have a check ,can you provide the below information(locale /language) as peter mentioned for

Thanks

15

Please find the below screenshot for your reference @Shikhar_Tandon
image

Locale/Language is English(Singapore)

16

image
Locale/Language is English(SIngapore) @ppr

17

@eshwarsai_ks

Check as below
Convert Date & Time Format Tutorial

Hope this helps you

Follow Link

Thanks

1 Like
18

Thanks for sharing @Srini84 This approach is taking more time as it has to iterate all the rows. I have more than 5k rows in my excel and sometimes it has 10k rows and that will take more time to complete. May I know is there any other way to complete without looping ?

19

Unfortunately i can only show with my locals

  • Select columns
  • change format
  • select custom format
    grafik
    grafik745×440 14.4 KB

    Within my locals it is provided as following
    T=Tag = Day
    M - Month
    j = Year

Just have a look on some samples from the list and you will get your local codes as well

  • Enter custom format
    Do use your local codes for day, month year

Ideally this should work. Then the found format string can be used within balarevas change cell format activity

1 Like
20

swaps to date.xlsx (957.6 KB) Can you pls help me with this attached excel ?