Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Excel Duration import to Power BI Issue

I need urgent help with my query. I have a excel data in given format. Duration = 123:47:00 custom formatted with [h]:mm:ss Actual value: 1/5/1900 3:47:00AM  Note: I do not have start date or e...
Need Help
artemus's avatar
artemus
Microsoft Employee
5 years ago

Change the data type to duration, not time. The time data type is for time of day.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

I tried that first. Didn't work.
Getting below error.

 

  • artemus's avatar
    artemus
    Microsoft Employee
    5 years ago

    Ah, sounds like the format has already been set by Excel to a datetime format. In that case, just create a custom column and subtract #datetime(1900, 0, 0, 0, 0, 0)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello,


      Note sure if I tried correctly as you said but getting error as shown below.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Below calculation worked but still off by 1 day.
        Currently getting 4days 3hrs 47 min. However its should be 5 days 3hrs 47 min = (5*24)120hrs + 3 hr = 123hrs 47 min.