Forum Discussion

shado26's avatar
shado26
Icon for Helper III rankHelper III
8 years ago
Solved

measure date difference power excluding weekend

Dear all   apprecite your assist and support to found way measure date difference power excluding weekend   please note that i have start date/time & End date/time  or i need to measure SLA for t...
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi shado26,

     

    You can download the file from here. Can you share your file please?

    measure_date_difference_power_excluding_weekend

     

    Best Regards,

    Dale

shado26's avatar
shado26
Icon for Helper III rankHelper III
8 years ago

Hi v-jiascu-msft

 

thank for your reply 

 

here screenshot from what i have 

https://imgur.com/a/FVAMt

 

i have start date/time & End date/time  and i need to measure SLA for these dates/ time 

moreover i have standred SLA 1h and 30 min to measure if employee has been Met SLA or not 

 

our weekend are friday and saturday

our working hours  from 8:00 AM till 5:00 PM 

 

 

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi shado26,

 

The formula of SLA could be like this. You can try it in this file.

SLA =
VAR allseconds =
    DATEDIFF ( [Start], [End], SECOND )
VAR days =
    INT ( allseconds / 24 / 60 / 60 )
VAR hours =
    MOD ( INT ( allseconds / 60 / 60 ), 24 )
VAR minutes =
    MOD ( INT ( allseconds / 60 ), 60 )
VAR seconds =
    MOD ( allseconds, 60 )
RETURN
    days & " days "
        & hours
        & " hours "
        & minutes
        & " minutes "
        & seconds
        & " seconds"
measure_date_difference_power_excluding_weekend2

I have to ask what is the expected result? Are the start date and the end date in one day? How many standard SLA? What are they?

 

Best Regards,

Dale

  • shado26's avatar
    shado26
    Icon for Helper III rankHelper III
    8 years ago

    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 "

     

     

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      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

      • shado26's avatar
        shado26
        Icon for Helper III rankHelper III
        8 years ago

        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