Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

PowerQuery Date/Time formating issue

I am having issue with the way date/time is formatted when in excel, looks fine in PowerQuery but then when i load into excel its messed up

 

in PowerQuery it shows as 11/25/2022 8:45:59pm

when i load into excel it shows like 44573.64493

 

i have the correct datatype selected in PowerQuery (Date / Time)

 

has anyone had this before?

 

thanks in advance 

 

 

 

 

2 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    3 years ago

    Syndicate_Admin 

     

    What you are seeing is the date/time as an Excel serial number.  You just need to format the cell(s) in Excel as date/time or use a custom format like d/mm/yyyy h:m:s

     

    Read more info on Excel Date and Time 

     

    Regards

     

    Phil

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      3 years ago

      thank you, and by manually changing the format to the column in excel does work, but then when i refresh the data again and reload to excel, i have to manually change it again... not sure if there is a setting i am missing so that when i close and load it keeps the data type for that column