Date format error in Power Query

Zaigham

Board Regular
Joined
Dec 22, 2010
Messages
159
Office Version
  1. 2021
Platform
  1. Windows
  2. Mobile
[FONT=&quot]Hi,
[/FONT]

[FONT=&quot]I have got cleaned and transformed a large data set generated by our server, in Excel 2016 through its "Get & Transform" feature. All was OK and amazing except the error in date format. Server had generated the date as "dd.mm.yyyy" format. The G&T is unable to format date greater than 12th December 2017 and show error. 1st December is treated as 12th January, 2nd December is treated as 12th February and so on up to 12th December. I have formatted it as text in G&T and after loading in Excel, applied the date format using "Datevalue" command.
[/FONT]

[FONT=&quot]Help is required to over come this date format problem.
[/FONT]

[FONT=&quot]Regards
[/FONT]

[FONT=&quot]Zaigham
[/FONT]
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
What kind of data source is it?
 
Upvote 0
Sorry I forgot to mention in my question. It was a CSV file having thousands of records.
 
Upvote 0
In the query editor window, if you click the dropdown to the left of the Home tab and then choose 'Options and settings' - 'Query options', you can change the Regional settings to ones that use the . as a date delimiter.
 
Upvote 0
Solution
Glad to help.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top