Excel Duration import to Power BI Issue

[Anonymous]

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 end date. I have total duration for each month.

When I try to import the data in Power BI. I am seeing abc format in 1/5/1900 3:47:00 AM. I can convert this to Date/time which represent the same value. 

When I change data type to Time, I see 3:47:00, which is not correct hours and min. 

How do I get the same duration 123:47:00 in Power BI?
I really tried multiple solution, nothing really worked.

Thank you for your help in advance.

[artemus]

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

[Anonymous]

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

[artemus]

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]

Hello,

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

[Anonymous]

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.