exceldatetimepowerquerydate-formattingdatetime-comparison

Day calculation from PowerQuery not correclty transformed to excel table

I am using PowerQuery to create a certain date time duration column. I have the following xlsx table:

Table1

DateTime1: =A2+B2

DateTIme2=D2+E2

DateDiff=F2-C2

Now I want to transform Column G "DateDiff" with PowerQuery. I select the Range A to G and click on Data => From Table/Rage

I am asked where the data is and I use the prefilled entries and just click on ok. PowerQuery opens:

PQ1

I select the column DateDiff and right-click and duplicate this column. A copy of this column appears and I select it. On datatype (where currently decimal is shown) I select "Duration". The values are transformed and I can see the following:

PoQuet

This is as expected. So first are the days, like for example 32 or 1 or 33 days, followed by the hours, minutes and seconds (and some rounding/calculation differnces in the seconds, but this can be ignored).

I click on close and load.

The table is added as follows:

Table2

The values were changed. Now column H does not show the 32,33 and 1 day anymore. The values are incorrectly shown as 1.07..., 2.08...

I do not understand why this happens? How can I get correct results here?

When I change the format to standard I get the correct numbers again. So the numbers were not changed. When I try to apply a custom format, like: T.hh:mm:ss the same wrong numbers shown. Leads to the identical problem.

Solution

  • It appears to be a formatting issue. Most likely, you have col H formatted as d.hh:mm:ss

    Unfortunately, Excel date d (day) format (or whatever the code for day is in your locale) will not display any value > 31. At that point it will wrap back to 1

    One way to get the display you want is to turn it into a string:

    =INT(A1) & "." & TEXT(MOD(A1,1),"hh:mm:ss")

    You could also turn it into a string using Power Query