Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
5 years ago
Solved

Subtract non-working hours

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 en...
  • V-lianl-msft's avatar
    5 years ago

    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)