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