Forum Discussion

GK600's avatar
GK600
Helper II
5 years ago

Date Format Error

Hi,   Hope someone can help me. I have looked through many forums but didn't come across this one.   My regional settings is English (United Kingdom) and Data load type detection is set to below....
AntrikshSharma's avatar
AntrikshSharma
Community Champion
5 years ago

GK600 The dates in your excel file are actually stored as text, you can tell that by the left alignment of a number. Numbers are always right aligned in excel a quick and dirty way is to do this, since excel is accessible to you. Once you covert them back to numbers using double negative, you can replace the original column with the new one:

Pragati11's avatar
Pragati11
Super User
5 years ago

Hi AntrikshSharma ,

 

I don't agree modifying date column from TEXT to NUMBER data-type in excel, because Power BI can easily handle the date values that come as Text through a excel file.

 

Like see below raw data from excel:

Now I can transform this TExt Date column to date type by using Locale option and it gets converted quite easily:

 

I used United Sates date format "Using Locale" because I know they are in that format, and Power BI easily converts the text to date value:

 

Here my default Date format is United Kingdom, therefore dates are shown in this format.

 

Let me know if I mis-understood your response. 🙂

 

Thanks,

Pragati