How to filter in excel power query between two time intervals

%3CLINGO-SUB%20id%3D%22lingo-sub-2675014%22%20slang%3D%22en-US%22%3EHow%20to%20filter%20in%20excel%20power%20query%20between%20two%20time%20intervals%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2675014%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202021-08-23%20at%209.05.06%20pm.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F305016i5EB83DD992E60046%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202021-08-23%20at%209.05.06%20pm.png%22%20alt%3D%22Screen%20Shot%202021-08-23%20at%209.05.06%20pm.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83Please%20can%20you%20help%20on%20this%2C%20I%20want%20to%20group%20this%20date%20modified%20into%20three%20different%20groups%20like%2C%20from%206.00%20am%20to%201.59pm%20and%202.00pm%20to%209.59pm%20and%2010.00pm%20to%206.00am%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2675014%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2675203%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20filter%20in%20excel%20power%20query%20between%20two%20time%20intervals%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2675203%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F317114%22%20target%3D%22_blank%22%3E%40krish86%3C%2FA%3E%26nbsp%3BHere%20is%20one%20way%20by%20using%20a%20calculated%20column.%20The%20trick%20I%20used%20is%20to%20first%20add%206%20hours%20to%20the%20date%2Btime%20so%20your%20periods%20start%20at%20midnight.%20Then%20divide%20by%203%20and%20take%20the%20integer%20of%20that%20so%20we%20get%20three%20%22periods%22%20of%208%20hours%20in%20a%20day%20and%20finally%2C%20subtract%20the%20six%20hours%20again%20to%20shift%20the%20time%20back%20to%20its%20%22original%22%20period.%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Screen Shot 2021-08-23 at 9.05.06 pm.png

 Please can you help on this, I want to group this date modified into three different groups like, from 6.00 am to 1.59pm and 2.00pm to 9.59pm and 10.00pm to 6.00am

1 Reply

@krish86 Here is one way by using a calculated column. The trick I used is to first add 6 hours to the date+time so your periods start at midnight. Then divide by 3 and take the integer of that so we get three "periods" of 8 hours in a day and finally, subtract the six hours again to shift the time back to its "original" period. See attached.