Forum Discussion

Murderface_'s avatar
Murderface_
Regular Visitor
6 years ago

Power Query time format '37:30:55' bug

Hello! I encountered the following problem when working in PQ with data in the format '37: 30: 55 '. If you try to retrieve data in this format using Excel.Workbook, the data may be incorrect. Please...
Need Help
v-eachen-msft's avatar
v-eachen-msft
Community Support
6 years ago

Hi Murderface_ ,

 

Actually, this is a bug in Excel about leap year.

You could refer to this link: https://en.wikipedia.org/wiki/Year_1900_problem

Microsoft Excel (using the default 1900 Date System) cannot display dates before the year 1900, although this is not due to a two-digit integer being used to represent the year: Excel uses a floating-point number to store dates and times. The number 1.0 represents January 1, 1900, in the 1900 Date System, or January 1, 1904, in the 1904 Date System and was the default for Macintosh prior to Excel 2016. Numbers smaller than this display as a #VALUE! error.
For compatibility with Lotus 1-2-3, the 1900 Date System incorrectly accepts February 29, 1900, however, 1900 was not a leap year.

So there isn't a "02/29/1900" date, because 1900 is not a leap year. 

 

Murderface_'s avatar
Murderface_
Regular Visitor
6 years ago
Good day! I know about the problem of 1900, then in my case we are talking about time in the format '37: 30: 55 ', and PQ treats it as a date and if this date is 29 feb 1900, then I lose 24 hours.