Forum Discussion
List hour intervals between timestamps
Hi All,
May I kindly ask for help?
I have a list of employees with their login and logout timestamps.
I need to know: How many employees were logged in (=available) at certain hour of day? E.G., how many employees were logged in at 7 PM, 8 PM, 9 PM, 10 PM, 11 PM, 12 AM, 1 AM, 2 AM etc.
This is a 24/7 support, which means that some of the employees' shifts will overlap to another day. Moreover, some employees logged in and logged out a few times during their shift, therefore there are more rows for one employee.
| Login Timestamp | Logout Timestamp | Employee |
| Tue, 31 Jan 2023 18:41:53 | Tue, 31 Jan 2023 18:42:34 | Employee 1 |
| Tue, 31 Jan 2023 18:43:21 | Tue, 31 Jan 2023 18:43:56 | Employee 1 |
| Tue, 31 Jan 2023 18:44:31 | Tue, 31 Jan 2023 20:00:43 | Employee 1 |
| Tue, 31 Jan 2023 18:53:50 | Wed, 1 Feb 2023 03:13:24 | Employee 29 |
| Tue, 31 Jan 2023 18:54:23 | Tue, 31 Jan 2023 19:45:05 | Employee 25 |
| Tue, 31 Jan 2023 18:54:31 | Wed, 1 Feb 2023 04:59:55 | Employee 8 |
| Tue, 31 Jan 2023 18:56:11 | Tue, 31 Jan 2023 18:57:02 | Employee 12 |
| Tue, 31 Jan 2023 18:56:13 | Tue, 31 Jan 2023 19:02:53 | Employee 9 |
| Tue, 31 Jan 2023 18:57:05 | Tue, 31 Jan 2023 18:57:32 | Employee 12 |
| Tue, 31 Jan 2023 18:57:54 | Tue, 31 Jan 2023 19:26:10 | Employee 12 |
| Tue, 31 Jan 2023 18:58:36 | Tue, 31 Jan 2023 19:30:27 | Employee 28 |
| Tue, 31 Jan 2023 18:59:20 | Tue, 31 Jan 2023 22:20:53 | Employee 16 |
| Tue, 31 Jan 2023 19:00:42 | Wed, 1 Feb 2023 05:05:42 | Employee 7 |
| Tue, 31 Jan 2023 19:03:05 | Tue, 31 Jan 2023 19:22:57 | Employee 4 |
| Tue, 31 Jan 2023 19:03:40 | Tue, 31 Jan 2023 19:26:09 | Employee 13 |
| Tue, 31 Jan 2023 19:04:10 | Tue, 31 Jan 2023 20:32:53 | Employee 9 |
| Tue, 31 Jan 2023 19:11:26 | Tue, 31 Jan 2023 19:11:58 | Employee 6 |
| Tue, 31 Jan 2023 19:26:13 | Tue, 31 Jan 2023 19:39:48 | Employee 4 |
| Tue, 31 Jan 2023 19:41:37 | Tue, 31 Jan 2023 19:42:12 | Employee 12 |
| Tue, 31 Jan 2023 20:38:48 | Wed, 1 Feb 2023 00:15:53 | Employee 9 |
| Tue, 31 Jan 2023 20:53:29 | Wed, 1 Feb 2023 02:16:52 | Employee 17 |
| Tue, 31 Jan 2023 21:10:36 | Tue, 31 Jan 2023 23:22:27 | Employee 24 |
| Tue, 31 Jan 2023 21:11:01 | Wed, 1 Feb 2023 03:29:05 | Employee 26 |
| Tue, 31 Jan 2023 21:23:59 | Wed, 1 Feb 2023 07:31:30 | Employee 2 |
| Tue, 31 Jan 2023 21:29:09 | Wed, 1 Feb 2023 01:00:05 | Employee 11 |
| Tue, 31 Jan 2023 21:29:33 | Wed, 1 Feb 2023 07:40:00 | Employee 5 |
| Tue, 31 Jan 2023 21:29:38 | Wed, 1 Feb 2023 07:32:17 | Employee 23 |
| Tue, 31 Jan 2023 21:31:33 | Wed, 1 Feb 2023 07:31:05 | Employee 18 |
| Tue, 31 Jan 2023 21:43:06 | Tue, 31 Jan 2023 23:07:27 | Employee 27 |
| Tue, 31 Jan 2023 21:43:14 | Wed, 1 Feb 2023 07:30:48 | Employee 14 |
| Tue, 31 Jan 2023 21:45:48 | Wed, 1 Feb 2023 07:32:29 | Employee 21 |
| Tue, 31 Jan 2023 22:03:13 | Tue, 31 Jan 2023 22:05:49 | Employee 3 |
| Tue, 31 Jan 2023 22:30:39 | Wed, 1 Feb 2023 03:36:53 | Employee 16 |
| Tue, 31 Jan 2023 23:08:27 | Wed, 1 Feb 2023 07:30:41 | Employee 27 |
| Tue, 31 Jan 2023 23:42:08 | Wed, 1 Feb 2023 07:01:45 | Employee 24 |
| Tue, 31 Jan 2023 23:47:27 | Wed, 1 Feb 2023 09:48:27 | Employee 20 |
| Tue, 31 Jan 2023 23:51:06 | Wed, 1 Feb 2023 10:00:37 | Employee 19 |
| Tue, 31 Jan 2023 23:51:33 | Wed, 1 Feb 2023 02:59:18 | Employee 15 |
| Tue, 31 Jan 2023 23:51:41 | Wed, 1 Feb 2023 09:11:49 | Employee 22 |
| Tue, 31 Jan 2023 23:54:31 | Wed, 1 Feb 2023 10:00:14 | Employee 10 |
The source data is csv. The output is supposed to be an Excel file. I am trying to figure this out in Power Query.
Thanks everybody in advance!
1 Reply
- ImkeF
Community Champion
Hi Anonymous ,
the solution below includes 2 columns: One still has the date-component to it and the other one just displays the full hour:// Table1 let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Login Timestamp", type datetime}, {"Logout Timestamp", type datetime}, {"Employee", type text}}), #"Inserted End of Hour" = Table.AddColumn(#"Changed Type", "End of Hour", each Time.EndOfHour([Login Timestamp]), type datetime), #"Renamed Columns" = Table.RenameColumns(#"Inserted End of Hour",{{"End of Hour", "FullHourLogin"}}), #"Inserted Start of Hour" = Table.AddColumn(#"Renamed Columns", "Start of Hour", each Time.StartOfHour([Logout Timestamp]), type datetime), #"Renamed Columns1" = Table.RenameColumns(#"Inserted Start of Hour",{{"Start of Hour", "FullHourLogout"}}), #"Inserted Time Subtraction" = Table.AddColumn(#"Renamed Columns1", "NoOfHours", each if [FullHourLogout]> [FullHourLogin] then Duration.Hours([FullHourLogout] - [FullHourLogin]) else null), #"Added Custom" = Table.AddColumn(#"Inserted Time Subtraction", "Hours", each if [NoOfHours] = null then null else {0..[NoOfHours]}), #"Expanded Hours" = Table.ExpandListColumn(#"Added Custom", "Hours"), #"Added Custom1" = Table.AddColumn(#"Expanded Hours", "FullHour", each if [NoOfHours] <> null then [FullHourLogin] + #duration(0,[Hours],0,0) else null), #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom1", "FullHour", "TimePart"), #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"TimePart", type time}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"FullHourLogin", "FullHourLogout", "NoOfHours", "Hours"}), #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"FullHour", type datetime}}) in #"Changed Type2"Please also check the file enclosed.
