Calculate Hours between dates for only business hours

[jshinnenkamp]

I've got two date columns (created and modified), and I can calculate the hours between them simply using subratction. The issue is that I don't want to include weekend hours along with hours from 5pm to 7am.

So if the two dates are 10/20/2017 11am  and 10/23/2017 8am I would like it to return 7. Now it returns 69.

Any help would be appreciated, Thanks in advance!

[v-yulgu-msft · Accepted solution]

Hi @jshinnenkamp,

Open Query Editor mode, refer to below steps.

1. Add two custom columns to get the date part from [Created] and [Modified].

CreatedDate=DateTime.Date([Created])

ModifiedDate=DateTime.Date([Modified])

2. Change the data type of new columns from Date to Whole Number.

3. Add a new custom column [DateRange] and expand it. After expanding, remember to change data type of [Create date], [Modified Date] and [Date Range] to Date.

DateRange={[Created date]..[Modified Date]}

4. Create a custom column to get the weekday using below code. And filter this new column with below condition.

WeekDay=Date.DayOfWeek([Date Range])

5. Create two custom columns.

StartTime= if [Date Range]=[Created date] then Time.Hour([Createed]) else 7
EndTime= if [Date Range]=[Modified Date] then Time.Hour([Modified]) else 17

6. Similar to above step3, create a list column and expand it.

TimeRange={[StartTime]..[EndTime]-1}

7. Save and close all above changes. Then, in data view mode, create a new calculated table.

Result Table =
SUMMARIZE (
    'Hour Table',
    'Hour Table'[Createed],
    'Hour Table'[Modified],
    "Hour Interval", COUNT ( 'Hour Table'[TimeRange] )
)

Best regards,
Yuliana Gu

[Anonymous]

Hello @v-yulgu-msft ,

In my particular case I have data related to Service Ticket Creation (created) and Service Arrival Date (modifed). I may have multiple Service Arrival Dates as follows:

1. Technician scheduled but has not arrived, this will give me a date of 30-12-1899

2. Technician arrived, this will give me an arrival date other than 30-12-1899

3. Technician rescheduled for a future date, this will give me a date of 30-12-1899

How can I modify the Modified Date in your example to take the first/earliest date that is not 30-12-1899?

[sneddon65]

Is it posssible to do a calculation within the hour table instead of having another table - result table. I have other data in the hour table which I want to use but cannot connect the tables using a merge or append query as there is no suitable field. Kind regards

[Anonymous]

Hello,

I am performing the listed steps, but when I'm introducing 7, PBI takes it as 19:00. Any sugestion?

Thank you

StartTime= if [Date Range]=[Created date] then Time.Hour([Createed]) else 7
EndTime= if [Date Range]=[Modified Date] then Time.Hour([Modified]) else 17

[omarevp]

Hi @v-yulgu-msft

What if "Created" field sometimes is an empty field?

i'm trying with your solution but there are some fields in my table that are empty in the first date (Created, in this case)

Im calculating working hours from two dates and i want to exclude non-working hours, weekends and holidays..

Thank you very much.