Calculate hours between two datetime excluding weekends and non working hours

[Anonymous]

Dear Experts,

I want to calculate hours between 2 dates , CreationDate and CompletionDate

• Please exclude Saturday and Sunday
• Include only 9:00 AM to 6:00 PM hours

Will be very very great if you can do it for me as I am unable to do this as not much expert and think that level. 

Please download Test Data and PBIX for below link.

https://www.transfernow.net/dl/20211204nDhVs72D

[parry2k · Accepted solution]

@Anonymous check this video that talks about a similar question.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

I know this is already solved so I'm just leaving this here for the future in hope it might help someone in the future 🙂

https://www.villezekeviking.com/calculating-handling-time-during-office-hours/

[Anonymous]

Dear @parry2k ,

I have applied all the steps as per directions given in your video but my hours calculaitons seems incorrect. For example -

I have created 1 ticket on 29-Oct-2021 09:55:00 AM , and closed it on 03-Nov-2021 10:00:00 AM then if I consider my working hours as below -

Start - 08:00:00 AM

End - 05:00:00 PM

Exclude - Sat and Sun 

Then total hours should be -

29-Oct (Thu) - 7 Hours

30-Oct (Fri) - 9 Hours

31-Oct (Sat) - 0 Hours

01-Nov (Sun) - 0 Hours

02-Nov (Mon) - 9 Hours

03-Nov (Tue) - 2 Hours, Then total hours should be ~27 Hours. But as per calculation it is showing 18.1 hours.

Below is the Measure that I have created :

Bus Hours Open =

Var BusHoursStart = CALCULATE(SELECTEDVALUE('Business Hours'[Time]), 'Business Hours'[Business Hours] = "Start")

Var BusHoursEnd = CALCULATE(SELECTEDVALUE('Business Hours'[Time]), 'Business Hours'[Business Hours] = "End")

Var BusHoursPerDay = VALUE(BusHoursEnd - BusHoursStart)*24

Var ThisStartTime = 'Jira Raw data'[Created Time]

Var ThisEndTime = 'Jira Raw data'[Completion Time]

Var StartDate = 'Jira Raw data'[Created]

Var EndDate = 'Jira Raw data'[Custom field (Completion Timestamp)]

Var FirstDayElapsedTime = SWITCH(TRUE(),

RELATED('Date Calender'[IsWorkingDay])=0,0,

ThisStartTime>=BusHoursEnd,0,

ThisStartTime<=BusHoursStart,BusHoursPerDay,

StartDate = EndDate && ThisEndTime < BusHoursEnd, ROUND((ThisEndTime-ThisStartTime)*24,3),

ROUND((BusHoursEnd-ThisStartTime)*24,3))

Var LastDayElapsedTime = SWITCH(TRUE(),

LOOKUPVALUE('Date Calender'[IsWorkingDay], 'Date Calender'[Date], EndDate)=0,0,

ThisEndTime<=BusHoursStart,0,

ThisEndTime>=BusHoursEnd,BusHoursPerDay,

StartDate = EndDate,0,

ROUND((ThisEndTime - BusHoursStart)*24,3))

Var FullWorkingDay = CALCULATE(sum('Date Calender'[IsWorkingDay]), DATESBETWEEN('Date Calender'[Date], StartDate+1, EndDate-1))

Var TotalHours = FirstDayElapsedTime + LastDayElapsedTime + FullWorkingDay*BusHoursPerDay

Return TotalHours

If you can help me with to correct hours, will be greatful. I have send me pbix file.

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

Please try this code

WorkHour =
VAR _allmin =
    GENERATESERIES(
        MAX( 'Test Data'[Creation Date] ),
        MAX( 'Test Data'[Completion Date] ),
        1 / 1440
    )
VAR _workmin =
    FILTER(
        ADDCOLUMNS(
            _allmin,
            "workday", WEEKDAY( [Value], 2 ),
            "worktime",
                IF( AND( HOUR( [Value] ) >= 9, HOUR( [Value] ) <= 17 ), 1, BLANK() )
        ),
        [workday] < 6
            && [worktime] = 1
    )
RETURN
    COUNTROWS( _workmin ) / 60

Result:

Need more attention:

Your created time is text from you provided pbix file, you should change it to date/time type. I recommend use the power query edito to shape it.

PBIX file in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-chenwuz-msft , I am getting below error while applied your givebn formula.

WorkHour = VAR _allmin = GENERATESERIES( MAX( 'Jira Raw data'[Created Date with time] ), MAX( 'Jira Raw data'[Custom field (Completion Timestamp)] ), 1 / 1440 ) VAR _workmin = FILTER( ADDCOLUMNS( _allmin, "workday", WEEKDAY( [Value], 2 ), "worktime", IF( AND( HOUR( [Value] ) >= 9, HOUR( [Value] ) <= 17 ), 1, BLANK() ) ), [workday] < 6 && [worktime] = 1 ) RETURN COUNTROWS( _workmin ) / 60

Please help.

[v-chenwuz-msft]

Hi @Anonymous ,

Does it work if your add columns [Created Date with time]  and [Custom field (Completion Timestamp)] ? Maybe you do not add relationships between 'Jira Raw data' table and the table where [Discripiton] comes from.

Best Regards

Community Support Team _ chenwu zhu

[Syndicate_Admin]

Dear @Syndicate_Admin , I am have tried it but not working. I am attaching my pbix file here. Kindly help me to get solution of it. Thanks a lot

https://sendgb.com/VYCO7SxJhsa

[Syndicate_Admin]

Dear @Syndicate_Admin , thanks a lot for helping. This is perfectly working for me. 🙂

[parry2k · Accepted solution]

@Anonymous check this video that talks about a similar question.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Dear @parry2k ,

Thanks to guide me and helped me to resolve my query. I did step by step with help of video and it works for me what I wanted. Below is the calculation of Measure That I implemented-

Bus Hours Open =

Var BusHoursStart = CALCULATE(SELECTEDVALUE('Business Hours'[Time]),'Business Hours'[Business Hours]="Start")

Var BusHoursEnd = CALCULATE(SELECTEDVALUE('Business Hours'[Time]),'Business Hours'[Business Hours]="End")

Var BusHoursPerDay = VALUE(BusHoursEnd - BusHoursStart)*24

Var ThisStartTime = 'Jira Raw data'[Created time]

Var ThisEndTime = 'Jira Raw data'[Completion time]

Var StartDate = 'Jira Raw data'[Created]

Var EndDate = 'Jira Raw data'[Custom field (Completion Timestamp)]

Var FirstDayElapsedTime = SWITCH(TRUE(),

RELATED(Cal[IsWorkingDay])=0,0,

ThisStartTime>=BusHoursEnd,0,

ThisStartTime<=BusHoursStart,BusHoursPerDay,

StartDate = EndDate && ThisEndTime < BusHoursEnd, ROUND((ThisEndTime-ThisStartTime)*24,3),

ROUND((BusHoursEnd-ThisStartTime)*24,3))

Var LastDayElepsedTime = SWITCH(TRUE(),

LOOKUPVALUE(Cal[IsWorkingDay],Cal[Date],EndDate)=0,0,

ThisEndTime<=BusHoursStart,0,

ThisEndTime>=BusHoursEnd,BusHoursPerDay,

StartDate = EndDate,0,

ROUND((ThisEndTime-BusHoursStart)*24,3))

Var FullWorkDay = CALCULATE(sum(Cal[IsWorkingDay]),DATESBETWEEN(Cal[Date],StartDate+1,EndDate-1)) Var TotalHours = FirstDayElapsedTime + FullWorkDay*BusHoursPerDay + LastDayElepsedTime

Return TotalHours

[parry2k]

@Anonymous well done, and thanks for sharing the solution with the community so that others can take advantage of it.

Good luck!

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡