How to change random date format to orginal date in excel file using VBA code in uipath

i have used this vba code to change date format in excel file on particular sheet

Sub ToDate()
Dim LR As Long, i As Long
LR = Range(“A” & Rows.Count).End(xlUp).Row
For i = 2 To LR
With Range(“A” & i)
.NumberFormat = “dd/MMM/yyyy”
.Value = DateValue(.Value)
End With
Next i
End Sub

but it throws an error at .Value = DateValue(.Value)

i have also attached excel file please refer bellow and any help could be appreciated


@kaavya - Please check this post for reference…

This is how we can convert OADate…


1 Like

it has 30 rows to change and value keep increasing by 1 so, how can i give format for that in FromOADate(?).tostring(“dd/mmm/yyyy”)

I was just showing the sample on how to do it…I already shared the post where xaml has attached …Have you downloaded and checked?? if not , I would advice to do that first…

1 Like

i have downloaded and i got exception

Invoke code: Exception has been thrown by the target of an invocation.

i have given like this because i don know exactly how to give from date format here as it has in some number format?

@kaavya - 44317 is just sample. Instead of that you have pass your row value by converting that to double.

This is how your invoke code should be…

dtdate.AsEnumerable().ToList().ForEach(Sub(row) row("YourColumnName")=Datetime.ParseExact(Datetime.FromOADate(cdbl(row("YourColumnName"))).ToString("dd/MMM/yyyy"),"dd/MMM/yyyy",system.globalization.CultureInfo.InvariantCulture))

Hope this helps…

OR …if you have only less # of rows then we can do it in the traditional for each method…if you would like to take this avenue please let me know…i will show the steps…

still getting above mentioned exception @prasath17

@kaavya - I guess the quotes is missing before dd

Please check the “For Each” Sample

1 Like

@kaavya - Please try it…As a last resort please share your excel sheet by removing/masking all the sensitive info.

1 Like

sure @prasath17

@kaavya - Please hold on…I guess in your case …Datetime.parseexact is not necassary…

ConvertOADate_LZ.xaml (9.3 KB)
Test_LZ.xlsx (9.9 KB)

Please check the output in sheet3.

1 Like