The Number of Working Hours Between two Times

Power Query
1

Hi there,

image

I need help. I have different processes for instance from when an Opportunity to Feasibility. What I want to work out is how many WORKING HOURS (not duration) were between these two dates…

Our Working hours start at 8:00 and end at 16:30.

image
image761×242 12.9 KB

I want to working out the elapsed hours but only for those hours that pertain to our working hours .

Opp Loaded Date
01 Apr 2020 00:30:03

So in this case. The Opportunity was loaded on 1 April 2020 at 00:30:03. I will only start counting the hours from 08:00:00 - 16:30 which is 8.5 hours.

2 April 2020 - 7 April = 7 * 8,5 =

Submitted to Feasibility
08 Apr 2020 22:52:00

2

Hi @SammiP

I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:

  • A clear explanation of the problem you are experiencing
  • A mockup of the results you want to achieve
  • Your current work-in-progress PBIX file
  • Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Not completing your data may sometimes cause delay in getting an answer.

Thank you!

3

Hi @SammiP,

Revisit your earlier post
https://forum.enterprisedna.co/t/working-hours-between-8-and-16-30/31415/6
.
As for your question below

Converting duration into total hours is easy, three clicks on the ribbon as depicted below.

image

Here’s my sample query

let
    Source = Table.FromColumns(
        {{#duration(0,5,1,40), #duration(1,2,49,27), #duration(0,8,30,0), #duration(0,0,30,00), #duration(3,8,30,0)}},
        type table [Duration = duration]
    ),
    TotalHours = Table.AddColumn(Source, "Total Hours", each Duration.TotalHours([Duration]), type number)
in
    TotalHours

I hope this is helpful.

1 Like