I have date column(column
T) in my excel as shown in below format
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.
Thanks in advance!
You can do it by using Convert.ToDateTime(row( “cancelled at”).ToString(“dd/mm/yyyy”))
Without looping u can’t parse the date
oh cool,Thanks for your quick response
@AshwinS2 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
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
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.
give a try on balarevas (Easy) Excel Component and provided activities:
getting last row index:
reformatting the cells.
@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
Will try this step and get back to you
@Shikhar_Tandon. Thank you so much
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
swaps to date.xlsx
Please find the screenshot and the excel sheet for your reference
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.
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
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
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
Will you pls share the code sample
Sure, just wanted to have a check ,can you provide the below information(locale /language) as peter mentioned for
Please find the below screenshot for your reference
Locale/Language is English(SIngapore) @ppr
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 ?
Unfortunately i can only show with my locals
select custom format
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
swaps to date.xlsx (957.6 KB) Can you pls help me with this attached excel ?