You could use method 1 in The Cross Join Collection as shown in attached workbook.
See also added lookup tables in attached spreadsheet.
Custom SQL (Excel Legacy Connector)
SELECT d.[Alarm ID] , d.[Occured at] , IIF(d.[Cleared At] IS NULL, NOW(), d.[Cleared At]) AS [Cleared At] , l.[Day Start] , l.[Day End] FROM [data$] d, [lookup day$] l WHERE d.[Occured at] <= l.[Day End] AND IIF(d.[Cleared At] IS NULL, NOW(), d.[Cleared At]) >= l.[Day Start]
Occured Time Start
MAX([Day Start],[Occured at])
Cleared Time End
MIN([Cleared At],[Day End])
I also added a few DATEDIFF calculations for measuring time duration (seconds, hours, days).
See more details in attached workbook.
Attached Workbook Version: 9.0
Good Grief! Thanks so much Kettan, You have given a perfect solution.
With this I can really resolve my problems. I have been struggling with this problem for a month (actually longer).
My problem is that I don't know how to use SQL, but I can read it (I was an RPG Programmer back in the 80s so can figure out what it means). So my next Bucket List item is to really learn SQL.
Again, thanks so much for your time and you cannot believe how happy I am to have a solution - the smile is stretching the skin on my face! Haha.
Thanks very much again... Shaun