Forum

"prior 1900 dates" ...
 
Notifications
Clear all

"prior 1900 dates" not Loading / converting properly from power query to Excel worksheet

3 Posts
3 Users
0 Reactions
140 Views
(@raj_kumar)
Posts: 3
Active Member
Topic starter
 

HI,

 

iam new to power query. When i want to load data to excel file from power query, Date column (pre 1900 years) not loading properly into Excel sheet.  Go through the attachment (E column) & suggest me the way to solve it.

 

regards

raj kumar

 
Posted : 17/03/2021 10:13 am
Philip Treacy
(@philipt)
Posts: 1631
Member Admin
 

Hi Raj,

Can you please explain what you mean by dates prior to 1900 are not loading properly.

Looking at your file I see a column of dates.  But most of these are negative values (date serial numbers) and you can't have negative dates.  If that's the issue then you need to fix these dates to be valid.

Regards

Phil

 
Posted : 17/03/2021 7:33 pm
(@mynda)
Posts: 4761
Member Admin
 

Hi Raj,

Excel doesn't recognise dates prior to January 1, 1900, however Power Query can work with dates prior. So, while working with the dates in Power Query make sure they are data type: date. Then before loading to Excel you need to convert the column containing dates prior to January 1, 1900 to data type: Text so that they can be displayed in Excel as a date. Note: you won't be able to perform any calculations on these dates in the Excel table as they'll be text.

Mynda

 
Posted : 18/03/2021 10:34 pm
Share: