Representation Between Excel and Power Query

[Mruizch21]

I'm experiencing an issue when working with a specific date and time value in Excel and Power Query. In Excel, when I read the data "2/25/1900 6:00:00 AM," the decimal representation is 56.25, which is the expected result. However, when I upload the same data to Power Query, the decimal representation becomes 57.25.

I've ensured that the regional and date format settings are consistent between Excel and Power Query. Could someone shed light on why this discrepancy might be occurring and how I can align the decimal representations between the two?

 

[RoryA]

Dates prior to 1st March 1900 in Excel will be one day off compared to PQ since Excel treats 1900 as leap year (due to historical compatibility issues) whereas PQ recognises that it was not a leap year.

 

[Mruizch21]

Thank you for explaining the discrepancy related to dates prior to March 1, 1900. Regarding the decimal data processing, I believe it's related to the same challenge. It appears that in the first stage of the upload, the data is being read in its date and time format. When I subsequently change it back to the decimal format, it adds '1' to the values, resulting in '57.25' instead of the original '56.25.'

Could you provide guidance on how to address this specific aspect of the challenge? I want to ensure that Power Query processes the data to '56.25' without introducing the additional '1.' considering the the excel that I am uploading the data point is in the decimal format 56.25. Is there a quick way to correct this?

 

[RoryA]

Check if the date is less than 1st March 1900. If it is, subtract 1 otherwise leave it alone.