Error in Date Format

[jharmel.carmona (Jhang Carms)]

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?

Please advise.

Thank you.

[Gokul001 (Gokul Balaji)]

HI @jharmel.carmona

Can you check out the thread

Convert cell or range value from text to number [Excel][Vb]

@AS07 - Here you go… My Input [image] My Output After running the macro… [image] VBA macro i used…(Write it in a notepad and saveas .vbsfile…) My workflow Sub macro() Range(“C:C”).Select 'specify the range which suits your purpose With Selection Selection.NumberFormat = “General” .Value = .Value End With End Sub [image]

Regards
Gokul

[Parvathy (PS Parvathy)]

Hi @jharmel.carmona

Try this:

1. 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.
2. Use a “For Each Row in Data Table” activity to loop through each row in the DataTable.
3. 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")

4. Use Write Range Workbook to write it back to excel.

hope it helps!!

[Gokul001 (Gokul Balaji)]

Have you tried with format cell activity

Check out this thres

https://forum.uipath.com/search?q=format%20cell%20activity

[Part 1] Modern Activities - Find/Replace Value | Format Cells

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. [image] Let Jump into the Tutorial we are look into some Modern activities Find/Replace Value Format Cells Note : We need to use Excel Process Scope and Use Excel File for all the above activities Excel Process scope Use Excel File …

Regards
Gokul

[Gokul001 (Gokul Balaji)]

You can also try with Invoke VBA activity @jharmel.carmona

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)

Regards
Gokul

[supriya117 (Supriya Allada)]

Hi @jharmel.carmona

Read Range (Output: dtData)

dtUpdatedData = (From row In dtData.AsEnumerable()
                    Let dateColumn = row("PR Creation Date").ToString()
                    Where dateColumn.Contains("-") AndAlso IsTwoDigitYear(dateColumn)
                    Select row).CopyToDataTable()

For Each Row (ForEachRow in dtUpdatedData)
    Row("PR Creation Date") = FormatDate(Row("PR Creation Date").ToString())

Write Range

[Yoichi]

Hi,

Can you try the following sample?

dt.AsEnumerable.ToList.ForEach(Sub(r)
    r("PR Creation Date") = r("PR Creation Date").ToString.Insert(  r("PR Creation Date").ToString.LastIndexOf("-")+1,"20")
End Sub
)

Sample
Sample20231004-4L.zip (9.3 KB)

Regards,