WorkingHours excluding weekends and Consider working hours between 9 to 17:30

[vidyarani]

Hi Guys,

I hope you can help me on this!
I am creating a working hour field in the front end  which should exclude weekends and consider working hours between 09 to 17:30

Formula used is:

=Interval(

(RangeMin(Frac(EndDate), MakeTime(17,30,00))

- RangeMax(Frac(StartDate), MakeTime(09,00,00)))

+ (NetWorkDays(StartDate, EndDate-1) * MakeTime(08,30,00))

)

The above formula works perfectly fine if StartDate  time and EndDate time falls within 09 to 17:30 but it fails when these times are outside working hours.

For instance:

StartDate:7/8/2020  17:41:00

EndDate:7/8/2020  18:03:49

Ideally as per the logic working hours for these dates should be 0 as they fall outside working hours but output I am getting is 23:49:00 which is wrong.

Note: I have  gone through all the posts over community but nothing worked out and also I can't create these calculations in the script as StartDate and EndDate are from two different tables and they are connected via the fact table.

Can someone help me on this?

Thanks and Regards,

Vidyarani

[edwin]

maybe you can add an if statement if both start date and end dates are inside the valid time range.  since the fields are in separate tables, you can add two fields:

if start date time is beyond 1730 then start date is invalid

if end date time is before 900 then end date is invalid

so in your expression's if statement you test if both indicator fields are VALID

you may need to test all possible scenarios

[rubenmarin · Accepted solution]

Hi @vidyarani, what is doing is:

- The rangemin gets 17:30

- The rangeMax gets 17:41

- The result is -11 minutes, I'm not sure why its shown as 23:49.

To solve this you can ad an if at start to check this case, like: If(Floor(StartDate)=Floor(EndDate) and Frac(StartDate)>MakeTime(17,30,00), 0, `YourExpression])

Another posible solution would be add a RangeMin() to RangeMax() and viceversa, like:

- RangeMax(RangeMin(Frac(EndDate), MakeTime(17,30,00)), MakeTime(09,00,00))

- RangeMin(RangeMax(Frac(StartDate), MakeTime(09,00,00)), MakeTime(17,30,00))

[vidyarani]

Hi Ruben,

Thank you so much for the response. Really appreciate it!

It works perfectly fine.

is it possible to implement same scenario in Script?

It would be great if you can share me the script.

Thanks and Regards,

Vidyarani

[rubenmarin]

Hi @vidyarani, I think you can do exactly the same in script to have it in a new field. Can you try it? 

Maybe you need to merge tables if StartDate and EndDate are in different tables