Calculation of working hours with Friday having a different schedule

[S_I_L]

Hello everyone,

I need to calculate working hours between two dates, excluding weekends and holidays, and knowing that on Fridays the working hours are different from the other days of the week.

The working hours are from Monday to Thursday, from 9:15 AM to 5:30 PM, and on Friday from 9:15 AM to 4:45 PM. Lunch break is every day from 12:45 PM to 2:15 PM.

My problem is the working hours on Fridays that I can't integrate into my formula. For other providers who have the same working hours from Monday to Friday, I used this formula:

interval(rangesum(
NetWorkDays(Created+1,"Data Presa in Carico"-1,$(vHolidays)) * MakeTime($(vWorkingHourPerDay)), // In between hours
if(NetWorkDays("Data Presa in Carico","Data Presa in Carico",$(vHolidays)),
Rangemin(rangemax(frac("Data Presa in Carico"),maketime($(vStartHour))),maketime($(vEndHour)))-
Rangemax(rangemin(frac("Data Presa in Carico"),maketime($(vStartHour))),maketime($(vStartHour))),0), // working hours last day
if(NetWorkDays(Created,Created,$(vHolidays)),
Rangemin(rangemax(frac(Created),maketime($(vEndHour))),maketime($(vEndHour)))-
Rangemax(rangemin(frac(Created),maketime($(vEndHour))),maketime($(vStartHour))),0), // working hours first day
if(NetWorkDays(Created,Created/*,$(vHolidays)*/) and floor(Created)=floor("Data Presa in Carico"),-MakeTime($(vWorkingHourPerDay))) // If same day then correct the hours
),'hh:mm').

Can you help me?

I am attaching a set of data in Excel.

Thanks

[marcus_sommer]

I don't want to say that's not possible to do it in this way but I would go with another approach by transferring the essential into the data-model and using appropriate master calendar-time tables.

This means mainly creating a table which creates a date and a time and date-time field in 15 minutes buckets. The holidays might be added with a mapping and weekday() would identify the weekdays which is used to flag the record as working-relevant or not (just 0/1). The minutes are then accumulated with an interrecord-function like previous() by considering the flag.

Within the UI it's then just picking the max. value of this accumulation and subtracting the min. value. For each provider you could create an own one and the concatenating them into a single table - by creating a key-field of the date-time + provider. Such table may only serve this purpose and being a further dimension-table parallel to the regular master-calendar and master time-table.

[hadari]

How did you write the bEndHour for 17:15 Pm for example? It doesn’t recognise hours that aren׳t fill hours.. did you write it like 1715?

[marcus_sommer]

The logic (in Qlik as well as the most other tools like Excel) behind dates and times is, that they are numbers - whereby a date is an integer starting with 1 on the 01/01/1900 and each day after it adds 1 to it and times are a frac of 1, for example a second is: 1/24/60/60.

If your dates and times are such numbers they could be formatted in each way and you could calculate with them. If a field-value comes as pure number you could simple format it like it's needed. If the field-values have a date/time related formatting this formatting must be the same as Qlik default-formatting (the interpretation variables at the script-start) to be recognized as date/times. If the formatting is different and it's not wanted to adjust the interpretation variables (which could be done multiples times within the script) then you need a conversion of the field-value which translate it into a number and which afterwards could be formatted again.

In regard to your time of 1715 you may do:

time(time#(1715, 'hhmm'), 'hh:mm') as myTime

[hadari]

Thanks for the quick replay. I managed to solve the problem with out using variables but I still get a negative result while my 2 timestamps are: 

*my weekend is friday and Saturday 

.2023 6:23:46 AM=status_end_time_reopen

al_all_option= 29.10.2023 7:57:04 AM

And the expression is :

rangesum(round(rangesum(

networkdays(status_end_time_reopen+2,status_end_time_tupal_all_option,$(V_holidays))*MakeTime(13,30),//in between hours

if(NetWorkDays(status_end_time_tupal_all_option+1,status_end_time_tupal_all_option+1,$(V_holidays)),

rangemin(RangeMax(frac(status_end_time_tupal_all_option+1),maketime(07)),MakeTime(20,30))-

RangeMax(RangeMin(frac(status_end_time_tupal_all_option+1),maketime(07)),maketime(07)),0),// working hours last day

if(NetWorkDays(status_end_time_reopen+1,status_end_time_reopen+1,$(V_holidays)),

RangeMin(RangeMax(Frac(status_end_time_reopen+1),maketime(20,30)),MakeTime(20,30))-

RangeMax(RangeMin(Frac(status_end_time_reopen+1),maketime(20,30)),MakeTime(20,30)),0),

if(NetWorkDays(status_end_time_reopen+1,status_end_time_reopen+1,$(V_holidays)) and floor(status_end_time_reopen+1)=floor(status_end_time_tupal_all_option+1),-MakeTime(13,30))//if same day then correct the hours

)*24,0.01))

I don’t understand why I get -12:30:00 instead of 00:40:00 min.

Please help!

[marcus_sommer]

Don't try something like that. Too much efforts and complexity for simple measurements.

Much easier is to use calendars/timetables in the data-model in which all wanted dates/times are flagged with 1 - excluded weekends, holidays, non-working hours and so on get 0. A following step may be to accumulate these 0/1 values against any period-fields.

Afterwards a simple sum() will be enough to get the duration in regard to the selections/conditions respectively with the reference to two accumulated values you get also the duration.

Here a link with more details: Re: Excluding Non operational Hour's - Qlik Community - 2463134