To All Who Are Willing to Help:
I had this column in my generated report “
PR Creation Date” and contained thousands of rows (~7k rows). I wanted to automate the date format of this column cells containing “ dd-MMM-yy” format but there was an error in its current state. Message Error shown: *This cell contains a date string represented with only 2 digits for the year. *
What I want is to convert these cells all at once as per Excel prompt suggestion:
Convert XX to 20XX. Doing this manually can be so tedious since it contains thousands of rows. So, automating it might lessen the job.
How can I get this done? What activities best suited to get this done all at once?
Can you check out the thread
@AS07 - Here you go…
My Output After running the macro…
VBA macro i used…(Write it in a notepad and saveas .vbsfile…)
Range(“C:C”).Select 'specify the range which suits your purpose
Selection.NumberFormat = “General”
.Value = .Value
Use the “Read Range Workbook” activity to read the data from the worksheet containing the “PR Creation Date” column. This will read the data into a DataTable variable.
Use a “For Each Row in Data Table” activity to loop through each row in the DataTable.
Use the below syntax in Assign activity:
CurrentRow("PR Creation Date") = DateTime.ParseExact(CurrentRow("PR Creation Date").ToString, "dd-MMM-yy", System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MMM-20yy")
Use Write Range Workbook to write it back to excel.
hope it helps!!
Have you tried with format cell activity
Check out this thres
Hello Everyone, Hope you are doing good
I would like to present this post who are looking for the Modern Activities
Very first thing is to Enable the Modern experience (from Classic)
Go to → Activities Panel → Click on Filter → Select Show Modern.
Let Jump into the Tutorial
we are look into some Modern activities
Note : We need to use Excel Process Scope and Use Excel File for all the above activities
Excel Process scope
Use Excel File
You can also try with Invoke VBA activity
dt.AsEnumerable.ToList.ForEach(Sub(x) x("PR Creation Date") = DateTime.ParseExact(CurrentRow("PR Creation Date").ToString, "dd-MMM-yy", System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MMM-20yy")).ToString.trim)
Read Range (Output: dtData)
dtUpdatedData = (From row In dtData.AsEnumerable()
Let dateColumn = row("PR Creation Date").ToString()
Where dateColumn.Contains("-") AndAlso IsTwoDigitYear(dateColumn)
For Each Row (ForEachRow in dtUpdatedData)
Row("PR Creation Date") = FormatDate(Row("PR Creation Date").ToString())
Can you try the following sample?
r("PR Creation Date") = r("PR Creation Date").ToString.Insert( r("PR Creation Date").ToString.LastIndexOf("-")+1,"20")
Sample20231004-4L.zip (9.3 KB)
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.