Identify if any part of a duration between 2 date/times is within working hours

[silverdale9999]

Bit stuck with this one!

I've been asked to provide a count of planned works that have any part of the planned duration, during working hours.

I have a Reference, and 2 date/times, and I want to derive a final column as a Yes/No.  Example data, with final column as the calculated column I want to return.

WorkID	Start	End	In working hours?
WO1122	11/01/2021 19:00	11/01/2021 23:00	No
WO1234	11/01/2021 16:00	11/01/2021 19:00	Yes
WO1135	11/01/2021 09:00	11/01/2021 14:00	Yes

WorkID	Start	End	In working hours?
WO1122	11/01/2021 19:00	11/01/2021 23:00	No
WO1234	11/01/2021 16:00	11/01/2021 19:00	Yes
WO1135	11/01/2021 09:00	11/01/2021 14:00	Yes

Working hours are 08:00 to 18:00, Monday to Friday.

I've got similar 'stuff' for working days, but that requires a calendar table with Working Days flagged.  I don't think having every possible date/time in a table to flag a working hour is the way to go here, but am a bit stuck on the logic.

Any clues/ideas?

Thanks so much!

[amitchandak · Accepted solution]

@silverdale9999 , Try a new column like

new column =
var _st = [Start].time // or time(hour([start]),minute([start]),Second([start]))
var _end = [end].time // or time(hour([end]),minute([end]),Second([end]))
return
if(_st<= time(18,0,0) && _end>=Time(8,0,0), "Yes", "No")

[silverdale9999]

Thanks!! This works.  (I've not fully tested, but seems to do what I am expecting 🙂 )