Pivot table time date sum

[stayntru]

Hello,

I am trying to figure out how to create a pivot table with date/time information and then have that information displayed as a total number of hours each day on the pivot table. In this example on 2/3 the pivot table would display 8 hours 48 minutes, 2/2 on the pivot table would display 8 hours 43 minutes

Event date/time

2/3/2022 15:33

2/3/2022 6:45

2/2/2022 15:39

2/2/2022 6:56

Thank you.

 

[GlennUK]

Your data is not arranged logically - you have the start and end timestamps for days in the same column. Why can't you have them in separate columns?

 

[stayntru]

Your data is not arranged logically - you have the start and end timestamps for days in the same column. Why can't you have them in separate columns?

 

[stayntru]

Unfortunately the report I receive has the column populated with the data like this. I have hundreds of rows that I am trying to create a pivot table for so that I can see this data more objectively. I understand off the data was separated into separate columns it would be much more user friendly to pivot but this is the challenge I’m faced with.

 

[GlennUK]

Then transform your data, using an extra column to identify start and end timestamps. I'd guess Power Query is a good bet for that, before attempting anything further. Are you familiar with Power Query in Excel?

 

[stayntru]

Then transform your data, using an extra column to identify start and end timestamps. I'd guess Power Query is a good bet for that, before attempting anything further. Are you familiar with Power Query in Excel?

I’m not familiar with power query. Is that a feature of excel or a separate software?
Thank you for your help with this.

 

[GlennUK]

On second thoughts, I can do it with a couple of extra columns, and a PivotTable calculation option:

 

[GlennUK]

second tab of Field Settings:

 

[stayntru]

second tab of Field Settings:

I tried a lot to use that yesterday and couldn’t get it to work. I was trying to get the end result calculation with it though and not to separate the columns. I’ll try this new way to separate the columns then pivot the separated column data.

It seems like a lot of work that I will need to do since I have a report that has multiple tabs each with this date/time combo column with hundreds of rows of data.