Forum Discussion
How to filter time into 3 shifts like 6am - 2pm, 2pm - 10pm and 10pm to 6am in excel
3 Replies
krish86 If answer in this post
is not what you had in mind, perhaps you can just add another table that maps every hour to a shift code to filter or group by. Then, in PowerQuery (which you seem to be using already), extract the hour component from the date-time stamp and merge it with the codes from the mapping table. Not as clever as the solution provided earlier by JKPieterse but perhaps more straight-forward.
In general it's not necessary to keep separate table with groups, they could be calculated as
= Table.TransformColumns(#"Inserted Hour", {{"Hour", each Number.Mod(Number.RoundDown((_-6)/8)+1,3)+1, Int64.Type}})However, table is useful if shifts have different hours long.
- HI Guys,
Thanks for your response and I have created new thread with detailed explanation on this.
Regards,
Krish
