Change format column from "General" to "Date"

[LZlz]

Hello everyone!

I have an excel file with over 20,000 lines and I want to change the format of a column from “General” to “Date”, but without using for each because it takes a long time, the file having so many lines …
Do you have any suggestions for me?

Thank you!

“General”

“Date”

[prasath17 (Guru)]

@LZlz - Please check this post…

Convert Column to DateTime Format after Merging

@Yudhisteer_Chintaram1 - Could you please try this… dtdate.AsEnumerable().ToList().ForEach(Sub(row) row("ASN Shipment Date")=Datetime.ParseExact(Datetime.FromOADate(cdbl(row("ASN Shipment Date"))).ToString("dd/MM/yyyy"),"dd/MM/yyyy",CultureInfo.InvariantCulture)) For me this is giving the output in dateformat but in M/dd/yyyy format, may be it will work for your case based on the country. Please try and let me know.

For your case, please use the below code in the Invoke code and Pass dtdate as in/out datatable variable in the “Edit Arguments”…

 dtdate.AsEnumerable().ToList().ForEach(Sub(row) row("YourColumnName")=Datetime.ParseExact(Datetime.FromOADate(cdbl(row("YourColumnName"))).ToString("M/d/yyyy"),"M/d/yyyy",CultureInfo.InvariantCulture))

[LZlz]

Thanks!

Gives me the error below

[prasath17 (Guru)]

@LZlz - Please add system.globlolization in the import tab.

or use in the code as system.globlolization.cultureinfo.invariantculture…

Please try and let me know…

[LZlz]

Now, gives me the error:

[prasath17 (Guru)]

@LZlz - If possible share your file with few rows on it(after masking any sensitive info). I will share the workflow …

[LZlz]

Test.xlsx (7.9 KB)
I have columns from A to AL, and the date is on column O. I want to format column O in “Data”. He is now “General.”

[prasath17 (Guru)]

@LZlz - here you go…
ConvertOADate_LZ.xaml (5.8 KB)

Output:
Test_LZ.xlsx (9.0 KB)

Hope this helps…

Note: When you are running the real file, you have to make sure you don’t have any blank values in that column. or else the conversion will fail. If you have blank values in the column then you have to filter that out first and then pass that to the invoke code…

[LZlz]

You helped me a lot!
Thank you very very much!

[kumar.varun2 (Varun Kumar)]

@prasath17

If I have known the DateTime.FromOADate() earlier it could have saved a lot of hassle in solving the problem in this post

Convert month data into day wise data

Hi @manjesh_kumar Please check the xaml attached if it solves your problem 90 Rows per Line.xaml (9.2 KB) Also, the input and the output you have sent are not correct. Please check cell D369 of your Input sheet. It is not null. Before running the workflow please delete the content of cell D369. [image] The output is also not correct [image] The highlighted values must be 32.26 (Am I right?) Please let me know if the solution works for you.

Thanks @prasath17
Learned an interesting method today.

[LZlz]

I would have another question. Can the font look and size be changed? I should have that excel file set to Arial 8. I’m just asking …
thanks,

[prasath17 (Guru)]

@LZlz

Can the font look and size be changed? I should have that excel file set to Arial 8. I’m just asking …

For Changing font and font size , you have to either use UiAutomation to send hotkey OR Balareva activities…Please refer below…

Can i change an Excel cell format without VBA plz?

Hi @Soudios You can do that by: Cell color: You can using Set Range color [image] Font color: You can use send hotkey Font Color, Size and more: Using BalaReva.EasyExcel.Activities (BalaReva.EasyExcel.Activities can help you to change the font size, font color, etc. This activity will help you to easily handle excel.) [image]