Working Hours Between two date/time fields

[Keston]

Hello,

Seeking the assistance of the forum, I have two date/time fields in the following format and the following script to calculate the working hours between 8 - 1700 excluding weekend. The script is calculating same day correctly however, if the dates spans between multiple days the hours are incorrect as see below.

Can someone help me modify this script or provide a solution for me? I'm really struggling here to get the working hours.

Same day calculation

Mutiple days

Script

WorkingHoursDuration =
VAR StartDateTime = [date/time]
VAR EndDateTime = [date/time]

-- Define working hours
VAR StartOfWorkday = TIME(8, 0, 0)
VAR EndOfWorkday = TIME(17, 0, 0)

-- Helper function to calculate working hours in a given interval
VAR CalculateWorkingHoursInInterval =
VAR IntervalStart = MAX(DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime)) + StartOfWorkday, StartDateTime)
VAR IntervalEnd = MIN(DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime)) + EndOfWorkday, EndDateTime)
RETURN
IF (
IntervalEnd > IntervalStart,
DATEDIFF(IntervalStart, IntervalEnd, MINUTE) / 60.0,
0
)

-- Adjust start and end times if they fall outside working hours
VAR AdjustedStartTime =
IF (
WEEKDAY(StartDateTime, 2) > 5 || StartDateTime < DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime)) + StartOfWorkday,
DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime)) + StartOfWorkday,
IF (
StartDateTime > DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime)) + EndOfWorkday,
DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime)) + EndOfWorkday,
StartDateTime
)
)

VAR AdjustedEndTime =
IF (
WEEKDAY(EndDateTime, 2) > 5 || EndDateTime > DATE(YEAR(EndDateTime), MONTH(EndDateTime), DAY(EndDateTime)) + EndOfWorkday,
DATE(YEAR(EndDateTime), MONTH(EndDateTime), DAY(EndDateTime)) + EndOfWorkday,
IF (
EndDateTime < DATE(YEAR(EndDateTime), MONTH(EndDateTime), DAY(EndDateTime)) + StartOfWorkday,
DATE(YEAR(EndDateTime), MONTH(EndDateTime), DAY(EndDateTime)) + StartOfWorkday,
EndDateTime
)
)

-- Calculate working hours if the interval is within the same day
VAR SameDayHours =
IF (
DATEVALUE(AdjustedStartTime) = DATEVALUE(AdjustedEndTime),

0
)

-- Calculate working hours for full working days between start and end dates
VAR FullDaysStart = DATE(YEAR(AdjustedStartTime), MONTH(AdjustedStartTime), DAY(AdjustedStartTime)) + 1
VAR FullDaysEnd = DATE(YEAR(AdjustedEndTime), MONTH(AdjustedEndTime), DAY(AdjustedEndTime)) - 1

VAR FullDaysHours =
IF (
FullDaysStart <= FullDaysEnd,
CALCULATE (
SUMX (
FILTER (
CALENDAR (FullDaysStart, FullDaysEnd),
WEEKDAY([Date], 2) <= 5
),
9
)
),
0
)

-- Calculate working hours for the start and end days
VAR StartDayHours =
IF (
DATEVALUE(AdjustedStartTime) < DATE(YEAR(AdjustedStartTime), MONTH(AdjustedStartTime), DAY(AdjustedStartTime)) + EndOfWorkday,
VAR IntervalStart = MAX(StartOfWorkday, AdjustedStartTime)
VAR IntervalEnd = DATE(YEAR(AdjustedStartTime), MONTH(AdjustedStartTime), DAY(AdjustedStartTime)) + EndOfWorkday
RETURN
CalculateWorkingHoursInInterval,
0
)

VAR EndDayHours =
IF (
DATEVALUE(AdjustedEndTime) > DATE(YEAR(AdjustedEndTime), MONTH(AdjustedEndTime), DAY(AdjustedEndTime)) + StartOfWorkday,
VAR IntervalStart = DATE(YEAR(AdjustedEndTime), MONTH(AdjustedEndTime), DAY(AdjustedEndTime)) + StartOfWorkday
VAR IntervalEnd = MIN(EndOfWorkday, AdjustedEndTime)
RETURN
CalculateWorkingHoursInInterval,
0
)

-- Total working hours
VAR TotalWorkingHours =
SameDayHours + FullDaysHours + StartDayHours + EndDayHours

RETURN
MAX(0, TotalWorkingHours)

[Keston]

I have the details, so working hours is 8 - 1700 GMT-4 Monday to Fridday.

[lbendlin]

Good.  What is your required granularity level - half hour slots ok, or does it need to go down to minute level?

Please provide a couple of sample intervals in usable format, not as a screenshot.

[Keston]

Down to the minute will be ideal.

[lbendlin]

This is immutable data so I created a calculated column.

Work hours = 
var workingdays = FILTER(CALENDAR([Created],[Assigned]),WEEKDAY([Date],2)<6)
var possibleworkingminutes = SELECTCOLUMNS(GENERATE(workingdays,GENERATESERIES([Date]*1440+480,[Date]*1440+1019)),"Value",[Value])
var fullinterval = GENERATESERIES(ROUNDDOWN([Created]*1440,0),ROUNDDOWN([Assigned]*1440,0)-1)
var actualworkingminutes = INTERSECT(possibleworkingminutes,fullinterval)
RETURN ROUNDDOWN(countrows(actualworkingminutes)/60,0) & Format(countrows(actualworkingminutes)/1440,":nn")

[Keston]

Thank you for this but for some strange reason I'm receiving the folowing error.

The start date in Calendar function can not be later than the end date.

[lbendlin]

You and I have different date formats. You use dd/mm/yyyy and I use mm/dd/yyyy.  Neither are good formats. Best to use ISO-8601 to avoid ambiguity.

[Keston]

Hey lbendlin

I'm still receiving this error: The start date in Calendar function can not be later than the end date. Is it possible to update the script to probably exclude these instances?

Thanks.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

[Keston]

Hi lbendlin,

I found an issue with my data, it appears that the assigned time field may be blank at times. I also simulated with the Work hours.pbix. How can we take this into consideration?

Thank you for your support.

[lbendlin]

You can choose to ignore these rows, or replace the blanks with an appropriate date. Not sure if "Today"  is appropriate for this?

[Keston]

Hey lbendlin,

So I added Now() to the blank field and this worked well.

However, now I observed the root of the issue, I have a few instances were the created date is later than the assigned date, I'm not sure why this would have occured.

But, do you have any recommendations to the script to exlucde these instances?

[lbendlin]

You could use ABS() to always get a positive duration.

[Keston]

Ok noted, just to be clear, I should replace CALENDAR() with ABS()?

[lbendlin]

var workingdays = FILTER(CALENDAR(MIN([Created],[Assigned]),MAX([Created],[Assigned])),WEEKDAY([Date],2)<6)

[lbendlin]

There's no need for any of this, you can use a simple INTERSECT. 

BUT - only if you clearly define what "working hours"  means  - which days, which time zones etc.  Do you have that information?