I have an issue. I need to calculate the average time spend from a robot processing data, but all the data it consumes is from websites that only work on certain dates and time.
So i need to create a rule for each extraction of it. I already figured out how to calculate the time possible for the extractions, but to calculate the average amount of time i need to exclude some days of it, cause the extraction is not possible on sundays and after 22h.
Example:
If(Match(Bank,'Bank X'),
If(date_start=date_end,
Interval(date_start-date_end,'hh:mm:ss'),
Interval(interval(Floor(date_start)+22/24,'DD/MM/YYYY hh:mm:ss')-(if(Hour_start>maketime(22,0,0),
Interval(Floor(date_start)+22/24,'DD/MM/YYYY hh:mm:ss'),
IF(Hour_start<Maketime(7,0,0),
interval(floor(date_start)+7/24,'DD/MM/YYYY hh:mm:ss'),date_hour_start))) +
Interval(date_hour_end-Timestamp(date_end&' 07:00:00','DD/MM/YYYY hh:mm:ss'),'hh:mm:ss')+
Interval(Timestamp((diference_of_days*15)&':00:00','hh:mm:ss'),'hh:mm:ss'),'hh:mm:ss'))) as Hours_spent
I first see if the robot finished the task in the same day, and just taked the diference between the hours to see how much time it spent.
After that i took first the hours spent in the first day, and then, the hours spended on the last day, and sum the hours between using the diference of days multiplied by the hours that it could work.
I need to know how to exclude hours spended on Sundays, cause this formula worked for the hole week.
I tried this:
Timestamp(Interval(If(match(Bank,'Bank1'),if(floor(date_hour_start) = floor(date_hour_end),
diference_of_days/*i used it cause its the same as 0*/,
if((diference_of_days< 1) and (Match(weekday(date_start),'dom')),
diference_of_days,
if((Match(weekday(date_end),'dom') and dif_dias < 2),
0,
(NetWorkDays(floor(date_hour_start),floor(date_hour_end))-2)))))*16,'hh:mm:ss'),'hh:mm:ss') as
diference_of_time_Bank1
I already searched a lot on community but i could not find anything that worked.Btw sorry by my poor english i tried my best this s**t is driving me crazy since tuesday.
Any tips, or points tag me here or even send me an e-mail (gabriel.fonseca@fontespromotora.com.br) maybe we could talk a little bit!
Thanks a lot 🙂
