Calculate Overlapping shift

[Anonymous]

My database is based of Job ID that can run during 1st, 2nd or overlap between two.

I am having difficulty getting accurate resuts for shifts due to overlap between shifts.  
1st shift 5 AM to 4.30 PM

2nd Shift 5.00 PM to 3.30AM

OFF Shift 3.30 AM to 5.00 AM.

What I want :

I want a forth result for "overlapping shift".

The custom column DAX I used (of course with help of this forum) is:

hift =

VAR d = TIME(HOUR('JobOrders (2)'[ActualStartTimeLocal]),MINUTE('JobOrders (2)'[ActualStartTimeLocal]),SECOND('JobOrders (2)'[ActualStartTimeLocal]))

VAR e = TIME(HOUR('JobOrders (2)'[ActualStartTimeLocal]),MINUTE('JobOrders (2)'[ActualStartTimeLocal]),SECOND('JobOrders (2)'[ActualStartTimeLocal]))

VAR c = TIME(HOUR('JobOrders (2)'[ActualEndTimeLocal]),MINUTE('JobOrders (2)'[ActualEndTimeLocal]),SECOND('JobOrders (2)'[ActualEndTimeLocal]))

RETURN

IF('JobOrders (2)'[ActualStartTimeLocal] <> BLANK() && 'JobOrders (2)'[ActualEndTimeLocal] = BLANK(), BLANK(),

IF(e>=TIME(3,30,01) && c<= TIME(4,59,59), "OFF SHFIT",

IF(d >= TIME(05,00,01) && c <= TIME(16,30,00), "1st", "2nd")))

Thank you.

[Anonymous]

HI @Anonymous,

I tied to simple your formula and add the conditions deal with the 'second shift' situation, you can test with the below formula if it helps:

shift =
VAR _start =
    TIMEVALUE ( Table[ActualStartTimeLocal] )
VAR _end =
    TIMEVALUE ( Table[ActualEndTimeLocal] )
RETURN
    IF (
        MIN ( _start, _end ) = BLANK (),
        BLANK (),
        IF (
            _start > TIME ( 3, 30, 00 )
                && _end < TIME ( 05, 00, 00 ),
            "OFF SHFIT",
            IF (
                _start >= TIME ( 05, 00, 00 )
                    && _end <= TIME ( 16, 30, 00 ),
                "1st",
                IF ( _start >= TIME ( 17, 00, 00 ) && _end <= TIME ( 3, 30, 00 ), "2nd" )
            )
        )
    )

Regards,

Xiaoxin Sheng