Custom Time Intervals

[Anonymous]

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.

[Anonymous · Accepted solution]

@Mariusz thank you, appreciate it

[Mariusz]

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.

[Anonymous]

@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]

Hi @Anonymous 

Sorry, yes I've adjusted my post.

Best Regards,
Mariusz

Please feel free to connect with me.

[Anonymous · Accepted solution]

@Mariusz thank you, appreciate it

[Anonymous]

Hi @Mariusz how do I exclude weekends (Sat & Sun) from this function? 

i.e. if Job_Booking_DateTime is on Sat or Sun, then exclude from calculation

[Nathaniel_C · Accepted solution]

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 

[Anonymous]

Thank you @Nathaniel_C , just wasn't sure where to wrap that formula in the current function. Thanks for clarifying, appreciate it.