Changing the Date Format in a excel column

Hi All,

I want to change the date format from"02-09-21" to “02-Sep-21” in a excel.
I tried but I am getting only for one cell but not for the entire column.
Please someoneDateFormat.xlsx (11.8 KB)
do needful.
Please Refer excel for the reference.

Hey @HeartCatcher

You can change it directly using Format cells and give your format

HI @Gopal_Tewari1 ,

I am asking in uipath not manually in excel.
Can you please explain a bit more please.?

@NIVED_NAMBIAR

we can do it with followoing activities
grafik
ensure that this is done out side any Excel Application Scopes.

Another option could be to do it with a macro executed by UiPath

2 Likes

Hi @HeartCatcher

Please find the code file below as per your data
Processing: TestExcel…
TestExcel.zip (17.0 KB)

1 Like

Hi @HeartCatcher

Try the Below Expression to Convert to Specified format.

DateTime.ParseExact(row("TRN_DT   ").ToString.Substring(0,10) ,"MM/dd/yyyy",System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MMM-yyyy")

image

Regards

2 Likes

Hi @HeartCatcher,

On top of everyone else’s suggestions, you could try adding an apostrophe ("'") to the beginning of your dates if they are in a string format. This will prevent Excel form auto-formatting your dates in the Formula Bar.

DateString = "'" + DateVar.ToString

1 Like

Hi @ppr,
what to mention in the Cell Format is “Date”??

Hi @william.coulson ,

Did you mean as in the attachment??

Hey @HeartCatcher

Is your Problem is still persist could you please try the attached code which i have shared .

@pravin_calvin,

Thanks very much.
Now I wanna write the output into a excel in column"K". can you guide on this


Getting an error like that.

Could you Please click on click details and share screenshot.

this is error is occur due to the different column name as i have removed the spaces from last in the column name. SO if you can just copy the column name from excel pass it inside the code it will resolve

1 Like

Hi @HeartCatcher

Use For each row in the properties create index variable

use write cell inside the body of the for each loop and

write Cell value as below ScreenShot.

image

Regards

It also worked thanks verymuch.

1 Like

Could you please mark as solution :slightly_smiling_face:
Cheers Happy Automation

Hi @pravin_calvin ,
I tried your method its working fine but it is taking to much of time.
i.e taking 15 minutes to write 675 records.
Time to be taken less.
Please help me on this.

Hi @HeartCatcher

If the excel has column Name it would be easy to write range!

As it would take less time compared to above Method!

Regards

Hi @pravin_calvin ,

Yes excel do have the column name.