Forum Discussion
Syndicate_Admin
Administrator
5 years agoSubtract 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...
- 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)
V-lianl-msft
Community Support
5 years agoPlease 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
Administrator
2 years agoNice day!!
Excuse me how I could add holidays to this same script?