Forum Discussion
measure date difference power excluding weekend
Hi shado26,
How to override it? The formula in my last post will show up "Error" in the column.
ifmet = VAR sla2 = DATEDIFF ( DUMP_DATA_FROM_PURCHASE[start], DUMP_DATA_FROM_PURCHASE[end], SECOND ) RETURN IF ( WEEKDAY ( DUMP_DATA_FROM_PURCHASE[start],2 ) IN { 5, 6 }, "Week End approval", IF ( TIMEVALUE ( DUMP_DATA_FROM_PURCHASE[start] ) < TIME ( 9, 0, 0 ) || TIMEVALUE ( DUMP_DATA_FROM_PURCHASE[end] ) > TIME ( 18, 0, 0 ), "done", IF ( sla2 < 0,"Error",if(sla2 <= 5400, "Met", "Failed" ) ) ))Best Regards,
Dale
hellow Dale
thank for your feedback we have only one SLA which 1h 30 min
moreover i want to exclude weekend which " friday & stauerday " and exclude working hours which "9:00 AM to 6 PM "
Hi shado26,
Can you post the full name of SLA? What does it stand for? Maybe the full name will help me understanding this scenario. The related question is how to determine if the time meets SLA?
Another question: do you mean the end date time minus the start date time and excluding the weekends and non-working hours?
For example:
2018-01-01 is Monday. So 2018-01-01 9:00:00, 2018-01-01 10:00:00, the SLA is 1 hours;
2018-01-05 is Friday. So the SLA is 0;
Best Regards,
Dale
hi dale
SLA mean service level agreement
our stranded SLA are 1h 30 min to process orders
so if i received any order i need to process it within 1h 30 min
so i need formula to calculate received time " DateTimeFrom" and approved time " DateTimeTo" and to exclude week and working hours
Ex i approved this with 18 Min
Hi shado26,
Please try out a calculated column like this. You can download a demo here.
ifmet = VAR sla2 = DATEDIFF ( [Start], [End], SECOND ) RETURN IF ( WEEKDAY ( [Start], 2 ) IN { 5, 6 }, "NA", IF ( TIMEVALUE ( [Start] ) < TIME ( 8, 0, 0 ) || TIMEVALUE ( [End] ) > TIME ( 17, 0, 0 ), "NA2", IF ( sla2 <= 5400, "Met", "Failed" ) ) )Notes:
All the signs can be changed.
NA: weekends.
NA2: either start or end is out of work hour.
Best Regards,
Dale