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 entered at 01:00 and the other at 09:00 how can I do that when this out of schedule the hours are subtracted?

EmptyCarSpot_0-1626364782596.png

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

  • V-lianl-msft's avatar
    V-lianl-msft
    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)

     

2 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    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)

     

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      2 years ago

      Nice day!!

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