Time format 37:30:55 greater than 24 hours

Power Query
1

If I have a excel column with time format 37:30:55 i.e. more than 24 hours format, how to load this data into Power BI using power query ?

example 26:15:00 is wrongly loading as 2:15:00 since after 24 hours its changes as zero. ideally it should be 26 hours and 15 mins.

less than 24 hours only works fine, >24 hours has issues. So how to convert it properly please?

2

Hi @Swamisuja,

You can fix this by changing the data type to duration, I loaded your examples 37:30:55 and 26:15:00

  1. Click on the data type icon
  2. Select duration
  3. End result

Duration

3

I tried that and its giving me error since its showing as 2/8/1900 2:15:00 PM

4

Please see if you have a Changed Type step, in the Applied Steps changing it into type datetime first.
If that is the case, remove or replace that step by type Duration for the column.

Otherwise please supply a sample of the file that contains this issue, so I can have a look. Thanks

5

Start-Date End-Date Duration
3/1/2019 10:24:00 AM 8/17/2019 1:11:00 PM 950:36:00
3/2/2019 10:11:00 AM 8/17/2019 1:05:00 PM 944:00:00

Data format are in:
Dates are in Custom m/d/yyyy h:mm
Duration is in Time 37:30:55

When I upload this data into Power query
Duration column with Date/Time format - its loaded as
image

If I change this to Duration format - it errors saying 2/8/1900 2:36:00 PM couldn’t be converted error

6

@Swamisuja

It’s hard to troubleshoot without a file containing the actual error you’re encountering, so please
share a small sample so I can better assist you.

I’ve used the date ranges you noted above and here’s my result, sample Duration.pbix (24.1 KB)

tDuration
tDuration.PNG1055×152 11.1 KB

Let me know how you get on with this.

7

Hi @Swamisuja, we’ve noticed that no response has been received from you since December 14, 2019. We just want to check if you still need further help on this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. You may reopen a new thread when the need arises.

8

Sorry I was out of town for some days. Sample-data.xlsx (71.0 KB)
I have attached my sample data. I have manually calculated in the xls for networking hours between start and end date.
When I upload this xls into power bi, the hours more than 24hrs has the impact and shows wrong data.

I shall try with your suggestion. Thanks for your updates and followup

10

What I am looking for is excluding off hours too. No Off hours, No weekends , No holidays and then calculate the either hours or mins between 2 dates:

Your solution works fine but off hours is not excluded. Please let me know, if there is any option for the same.

Thanks
Swami

11

.
Okay so Bill Szysz created a Power Query M function that does just that and here it is:

(WHStart, WHEnd, Start, End, ListOfHolidays) =>

let

    DStart             = Number.From(DateTime.Date(Start)), // start day
    TStart             = Number.From(DateTime.Time(Start)), // start time
    DEnd               = Number.From(DateTime.Date(End)),  // end day
    TEnd               = Number.From(DateTime.Time(End)),  // end time
    // List of days without saturdays and sundays
    ListOfNumbers      = List.Select({DStart..DEnd}, each Number.Mod(_,7)>1),
    // List of dates without holidays, saturdays and sundays 
    ListOfWorkingDays  = List.Difference(ListOfNumbers,ListOfHolidays),
    SumOfWorkingHours  = if DStart = DEnd then //checking if the start day is the same as the final day
                            if DStart = List.First(ListOfWorkingDays) then // checking if the start day is not a holiday (start day = end day)
                               List.Median({WHStart, WHEnd, TEnd}) - List.Median({WHStart, WHEnd, TStart}) // working hours if the start day = end day
                            else
                               0
                         else
                            (
                             if DStart = List.First(ListOfWorkingDays) then // checking if start day is working day (start day <> end day)
                                WHEnd - List.Median({WHStart,WHEnd,TStart}) // working hours (for start day)
                             else
                                0
                            )
                            +
                            (
                             if DEnd = List.Last(ListOfWorkingDays) then // checking if end day is working day (start day <> end day)
                                List.Median({WHStart,WHEnd,TEnd})- WHStart // working hours (for end day)
                             else
                                0
                            )
                            +
                      // sum of full-time working hours (start day, end day, holidays, saturdays and sundays excluded)
                            List.Count(List.Difference(ListOfWorkingDays,{DStart,DEnd}))*(WHEnd - WHStart)
in
    SumOfWorkingHours

.
Lets look at an overview of the Staging Queries in the PBIX first:

  1. FileLocation; change this into the location where you’ve saved the excel file and all queries in the PBIX file will be restored.
  2. ListOfHolidays; is used by the fxSum of Working Hours and all Dates need to be Int64.Type
  3. tHours; lists your workingday start- and endtime
  4. WDstart; parameter is used by the fxSum of Working Hours (selected from the tHours)
  5. WDend; parameter is used by the fxSum of Working Hours (selected from the tHours)
  6. fxSum of Working Hours; as shown above
    Duration%20Queries
    .
    And this is the result when applied to your data. Indicated by orange the nett result in duration (3), hours (4) and minutes (5) for the given start- (1) and end (2) date and time.
    Below that indicated by red you’ll see an example that only time between the WDstart & WDend parameters are contributing to the result in [Sum of working hours] (3)
    Nett%20Duration
    Nett%20Duration1209×415 48.8 KB

.
Here are your files. I hope this was helpful.
Sample-data (1).xlsx (73.3 KB)
eDNA Forum - Duration.pbix (59.7 KB)

2 Likes