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
thanks,
kaavya
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…
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 - Please try it…As a last resort please share your excel sheet by removing/masking all the sensitive info.
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.
