I have 2 timestamps fields- start and end. I want to calculate the time it takes from start to close date excluding holidays and Fridays and Saturdays.
also I want to include time's between 7 am and 20:30 PM.
I wrote this expression:
rangesum(round(rangesum( networkdays(start_pick_up_timestamp+2,end_pick_up_timestamp,$(V_holidays))*MakeTime($(vWorkingHourPerDay)),//in between hours if(NetWorkDays(end_pick_up_timestamp+1,end_pick_up_timestamp+1,$(V_holidays)), rangemin(RangeMax(frac(end_pick_up_timestamp+1),maketime($(vStartHour))),MakeTime($(vEndHour)))- RangeMax(RangeMin(frac(end_pick_up_timestamp+1),maketime($(vStartHour))),maketime($(vStartHour))),0),// working hours last day if(NetWorkDays(start_pick_up_timestamp+1,start_pick_up_timestamp+1,$(V_holidays)), RangeMin(RangeMax(Frac(start_pick_up_timestamp+1),maketime($(vEndHour))),MakeTime($(vEndHour)))- RangeMax(RangeMin(Frac(start_pick_up_timestamp+1),maketime($(vEndHour))),MakeTime($(vStartHour))),0), if(NetWorkDays(start_pick_up_timestamp+1,start_pick_up_timestamp+1,$(V_holidays)) and floor(start_pick_up_timestamp+1)=floor(end_pick_up_timestamp+1),-MakeTime($(vWorkingHourPerDay)))//if same day then correct the hours )*24,0.01)) as Business_Hrs_pick_up_time
I created 4 variables.
the issue is the vEndHour variable.
when I write 20 as the variable it calculate it correctly.
but I want it to be 20:30 PM.
I tried to write 20.5 or maketime(20,30,00) or num(TIME#('07:00','hh:mm')) but nothing is working.
