Forum Discussion

MarcUrdang's avatar
MarcUrdang
Post Patron
6 years ago
Solved

dates

my date in Excel is dd/mm/yyyy

 

PBI reads in then as mm/dd/yyy so my data only pulls through January for example.

 

How do I get PBI to read it so that the date gets read as per Excel .. or do I need to ammend Excel to mm/dd/yyyy

 

any advice would be appreciated.

thanks

Marc

Need Help

5 Replies

  • Tahreem24's avatar
    Tahreem24
    Super User
    6 years ago

    MarcUrdang ,

     

    Approach 1) Select your column --> go to modelling tab --> Data Type set to Date --> Format set to dd/mm/yyyy.

    Approach 2) Create one calculated column like below:

    Date = FORMAT('Table1'[DateColumn],"DD/MM/YYYY")

     

    Please give Thumbs up for support and accept this as a solution if it helps you!

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    If the formatting solutions do not work out. Then mark that column as text and create a new date column.

     

    Date formatted = mid(DD__MM__YY[Date],4,2) &"/"& left(DD__MM__YY[Date],2) & "/" & right(DD__MM__YY[Date],4)

     

    Mark this new column as the date. Also, create one more column based on DD/MM/YYYY. Mark that also has text. use that column to display date in that format. Make the mm/dd/yyyy date column as sort column of the new column

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi MarcUrdang ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


    Best regards,