How to change entire column date format from "dd-MMM-yyyy" to "MM/dd/yyyy" in one go

Help Studio
1

I have an excel where I have date in one or more columns in “dd-MMM-yyyy” format

I need to change it to “MM/dd/yyyy”

I know how to chnage it row by row, but thats taking a lot of timeis there a way to change the format of the entire column in one go just by providing a column name?

2

Hey,
Please try this
Datetime.ParseExact(row(“ColumnName”).ToString,{“MM/dd/yyyy HH:mm:ss”,“MM/dd/yyyy”},System.Globalization.CultureInfo.InvariantCulture,DateTimeStyles.None)
Thanks,
Rounak

1 Like
3

DT.Asenumerable.Tolist.Foreach(Sub(r) r(“DateColumn”)=Datetime.ParseExact(r(“DateColumn”).Tostring,“dd-MMM-yyyy”,System.Globalization.Cultureinfo.InvariantCulture).Tostring(“MM/dd/yyyy”))

implement this in Invoke code Activity

2 Likes
4

Hi @Krithi1,

You can use the code below by compiling it in the “invoke code” activity. You can put a kill activity in before of it. (excel)

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim ws As Microsoft.Office.Interop.Excel.Worksheet
excel = New Microsoft.Office.Interop.Excel.Application
wb = excel.Workbooks.Open(inputFilePath, [ReadOnly]:=False)
excel.Visible = False
ws = CType(wb.Sheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet)
ws.Activate()
ws.Range("A:A").NumberFormat = "MM/dd/yyyy"
wb.Save()
wb.Close()
excel.Quit()

inputFilePath = your excel file path
Sheet1 = your excel sheet name
A:A = the column whose format you want to change.
MM/dd/yyyy = your format

5

This is what I tried and this does row by row which takes lot of time.

6

Hi @Gangadhar_Athili - May I know what “r” means here?

7

@muhammedyuzuak - sure, I will this code and let you know. Thank you.

8

@muhammedyuzuak

I am facing an issue here. My xlsx file column has date in the following format “01-JUN-2022” which is not working with the code you gave me. But it’s definitely working if I have the date format as “1-Jan-22”. Is there anything I can do to work for this format too - “01-JAN-2020”?

9

Hello @Krithi1

How you are getting this data in the excel?? Are you getting it as Datatable and writing to excel??

If yes, you can keep a static excel template in which format of the columns predefined. It will help to auto change the format of the string as you are pre defining.

10

@Rahul_Unnikrishnan

I have a csv file, in which the date format is listed in the columns as "1-Jun-22’. I am then using “read csv” activity to read the data and writing it to the excel, where the dates are changing to "01-JUN-2022’ in excel. This conversion from csv to excel is creating this date format problem.

But, the code that @muhammedyuzuak provided is not looking for any specific date format type upfront correct? Whatever date format it gets, it should still convert to ‘MM/dd/yyyy’ per my understanding ?

11

Hi @Krithi1,

I am sharing an article with examples of using .NumberFormat. You can review. The critical event here is to simulate the custom formats given in excel. Vb support covers them.

Regards,
MY

12

Thats a variable you are assiging , u can take any value instead of r , it can be x , y z, anything .

13

This is almost a two year old post you’re digging up.