Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jshinnenkamp
Frequent Visitor

Calculate Hours between dates for only business hours

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!

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

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])

1.PNG

 

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

2.PNG

 

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]}

3.PNG4.PNG

 

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.PNG

 

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.PNG

 

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

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

7.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yulgu-msft
Employee
Employee

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])

1.PNG

 

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

2.PNG

 

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]}

3.PNG4.PNG

 

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.PNG

 

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.PNG

 

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

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

7.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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?

 

 

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
Not applicable

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

  

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors