Subtract non-working hours

[Syndicate_Admin]

Hello my doubt is how to make the hours are subtracted in non-working hours, aca an example:

I am subtracting the hours between the shipping date and the current date but the first result was entered at 01:00 and the other at 09:00 how can I do that when this out of schedule the hours are subtracted?

ps: working hours are between 09:00 and 18:00

[V-lianl-msft · Accepted solution]

Please create a new column:

Hours2 = 
var start_date = 'Table (2)'[start]
var end_date = 'Table (2)'[end]
var Hours_List = SELECTCOLUMNS(GENERATESERIES((8), (16)), "hour", [Value])
var Minutes_List = SELECTCOLUMNS(GENERATESERIES((0), (59)), "Minute", [Value])
var Dates_List = CALENDAR(MIN('Table (2)'[start]),TODAY())
var HoursandMinutes = ADDCOLUMNS(
CROSSJOIN(Hours_List, Minutes_List),
"Time", TIME([Hour], [Minute], 00),
"Validity", IF([Hour]<9 || [Hour]>18,"Non working","Working")
)
var DateTimeTable = ADDCOLUMNS(CROSSJOIN(HoursandMinutes,Dates_List),
"weekday", WEEKDAY([date],2),
"New combined date",CONVERT(CONCATENATE([date],CONCATENATE(" ",[Time])),DATETIME)
)

var minutesfromstart = COUNTROWS(FILTER(DateTimeTable,start_date>=[New combined date] && [Validity]="Working" && [weekday]<6))
var minutesfromend = COUNTROWS(FILTER(DateTimeTable,end_date>=[New combined date] && [Validity]="Working" && [Weekday]<6))
var minutes = minutesfromend-minutesfromstart
var hours = DIVIDE(minutes,60)
return
IF(start_date=BLANK() || start_date>end_date || end_date=BLANK(),BLANK(),hours)

[Syndicate_Admin]

Nice day!!

Excuse me how I could add holidays to this same script?