Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Custom Time Intervals

Hi,

 

I've got a few DateTime columns:

  • Job Booking datetime
  • Attempted Acceptance datetime
  • Acceptance datetime

For the time differences between them e.g. time between Job Booking to Attempted Accepted, time between Job Booking to Acceptance; I need to categorize these time differences into the following time intervals:

  • 0-2hrs
  • 2-4hrs
  • 5-12hrs
  • 12-24hrs
  • >24hrs

Here is sample data

 

Hope that made sense. Appreciate any help.

 

  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    Hi Anonymous ,

    If (WEEKDAY(Date, 2) = 6 || WEEKDAY(Date,2) = 7, “”,The formula applied above)

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel 

7 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi Anonymous 

     

    You can try to add two columns like below.

    Acceptance = 
IF( 
    NOT ISBLANK( 'Table'[ACCEPTANCE_DATETIME] ),
    VAR _hrs = HOUR( 'Table'[ACCEPTANCE_DATETIME] - 'Table'[JOB_BOOKING_DATETIME] )
    RETURN 
        SWITCH(
            TRUE(),
            _hrs >= 0 && _hrs <= 2, "0-2hrs",
            _hrs >= 3 && _hrs <= 4, "3-4hrs",
            _hrs >= 5 && _hrs <= 12, "5-12hrs",
            _hrs >= 13 && _hrs <= 24, "13-24hrs",
            ">24hrs"
        )
)
    Attempted = 
IF( 
    NOT ISBLANK( 'Table'[ATTEMPTED_ACCEPTANCE_DATETIME] ),
    VAR _hrs = HOUR( 'Table'[ATTEMPTED_ACCEPTANCE_DATETIME] - 'Table'[JOB_BOOKING_DATETIME] )
    RETURN 
        SWITCH(
            TRUE(),
            _hrs >= 0 && _hrs <= 2, "0-2hrs",
            _hrs >= 3 && _hrs <= 4, "3-4hrs",
            _hrs >= 5 && _hrs <= 12, "5-12hrs",
            _hrs >= 13 && _hrs <= 24, "13-24hrs",
            ">24hrs"
        )
)

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Mariusz thank you for quick response. I notice the two column functions are exactly the same or am I missing something?

       

      Or did you mean one column for Acceptance datetime and the other for Attempted Acceptance datetime?

      • Mariusz's avatar
        Mariusz
        Community Champion
        6 years ago

        Hi Anonymous 

         

         

        Sorry, yes I've adjusted my post.

         

         

        Best Regards,
        Mariusz

        Please feel free to connect with me.
        Mariusz Repczynski