How to fix date column in excel?

I have an excel file where ‘Date’ column has values in “MM/dd/yyyy” format. I want to update the dates to save them as “dd-MM-yyyy” format. For this I read excel file through Read Range in a datatable and then For Each Row,
row(“Date”) = CDate(row(“Date”).ToString).ToString(“dd-MM-yyyy”).

Then wrote it back to Excel with Write Range. Value under Date column did change to ‘ddMMyyyy’ format. But some of them shows as “dd-MM-yyyy”(expected) and others show as “dd/MM/yyyy”. Like in below. How do we fix this to them unique.

tableCompare.xlsx (9.7 KB) DateFormatChange.xaml (4.9 KB)

Try this after updating all values…! @Sachingoudar

Thanks!

1 Like

@kadiravan_kalidoss Thank you. But I did not get it right. What is in this dateFormat.txt ? Could you pls attach that as well?

Do you know why my above code did not handle this formatting at first place?
ow(“Date”) = CDate(row(“Date”).ToString).ToString(“dd-MM-yyyy”).

dateFormat.txt (120 Bytes)

my apologies for late response…! @Sachingoudar

Thanks!

Not a problem, thanks for the reply.

I think it is just changing the display of the date values but not actually changing the dates to dd-mm-yyyy format.

Here is my input file:

O/p file:
image

@kadiravan_kalidoss My bad, ignore my previous comment. I had placed Invoke VBA incorrectly.

After placing it immediately after ‘Write Range’ activity, it fixed the display in excel for all values.

Thank you for that input.

-Sk

2 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.