Unanswered

SQL Query to excel changes Date format on DataTable

(0) Share

Report

Posted on by franco2

I hav an excel file with dates dd-mm-yyyy format. I'm doing a sql query to de excel and then pasting the data to a new excel file.

The Original data look like this:

Inside the data table it changes to mm-dd-yyyy format:

I have tried changing it back with a VBScript in the new excel file. But it does not work because when the data is pasted is set to dd-mm-yyyy format automatically, so it thinks the mm is dd.

If anyone can help me I would gladly appreciate it.

Thanks

Categories:

Power Automate Desktop

I have the same question (0)

All responses (5)

Answers (0)

Sort by

Deenuji_Loganathan_ 6,255 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@franco2
It appears that your system date format is set to "mm-dd-yyyy," which may be causing changes in your flow as well. If your system date format is "mm-dd-yyyy," consider switching it to "dd-mm-yyyy" format. This adjustment should resolve the issue.

Was this reply helpful? Yes No
franco2 33 on at

Like
a
(0)

Report
Copy link

Link copied!

I just checked the setting and they are already in dd-mm-yyy format.

Was this reply helpful? Yes No
Deenuji_Loganathan_ 6,255 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@franco2
Then macro is another option.
Please refer the below link - https://powerusers.microsoft.com/t5/Power-Automate-Desktop/How-to-use-date-formatting-in-datatable/m-p/2638640#M37675

Was this reply helpful? Yes No
franco2 33 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you very much! . I will do a macro as you suggest, seems to be the way.

Was this reply helpful? Yes No
Deenuji_Loganathan_ 6,255 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@franco2

Please try macro like below(Change as required) and let us know.
Sub ChangeDateFormat() Dim ws As Worksheet Dim lastRow As Long Dim rng As Range Dim cell As Range ' Set the worksheet where your data resides Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your actual sheet name ' Find the last row in column G lastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row ' Set the range for column G Set rng = ws.Range("G1:G" & lastRow) ' Loop through each cell in the range For Each cell In rng If IsDate(cell.Value) Then ' Change the date format to "dd-mm-yyyy" cell.Value = Format(cell.Value, "dd-mm-yyyy") End If Next cell End Sub

--------------------------------------------------------------------------------------------------------------------------
🔍Found my answer helpful? Please consider marking it as the solution! Thank you! 🙌

Was this reply helpful? Yes No