Time difference HELP

[demonfc]

Hello, 

I am looking to determine the difference in hours on a given day for a salesman who works that day. The expected result would be essentially be a seperate table with the salesmans name, the date, and the difference between the hours worked in that day. 

The table below is a sampling of the data but this would need to scale to around 1000 people. . 

Agent              CustomerName                      Date

Billy	Random Customer	10/27/18 6:21 AM
Billy	Random Customer	11/4/18 7:26 AM
Billy	Random Customer	10/9/18 04:13:AM
Billy	Random Customer	10/9/18 05:43:AM
Billy	Random Customer	10/9/18 03:33:AM
Billy	Random Customer	10/9/18 01:13:AM
Bob	Random Customer	10/27/18 12:27 AM
Bob	Random Customer	10/27/18 2:02 AM
Bob	Random Customer	10/27/18 4:26 AM
Bob	Random Customer	10/27/18 7:26 AM
Bob	Random Customer	10/27/18 7:26 PM
Bob	Random Customer	10/27/18 10:26 PM
Bob	Random Customer	10/11/18 7:16 AM
Bob	Random Customer	10/11/18 2:16:03PM
Bob	Random Customer	10/11/18 07:16:03PM
Bob	Random Customer	10/11/18 1:36:03PM
Bob	Random Customer	10/11/18 1:21:03PM
Bob	Random Customer	11/1/18 1:16 PM

Any help you can provide would be greatly appreciated. 

Thank you

[v-shex-msft · Accepted solution]

HI @demonfc,

In my opinion, I'd like to suggest you extract hour part from datetime column. Then you can create time table with time value from 0:00 to 23:00, build relationship from time table to new 'hour' column.

Column formula:

Hour = TIME(HOUR([Date]),0,0)

Calculate table formula:

Time table = GENERATESERIES(TIME(0,0,0),TIME(23,0,0),TIME(1,0,0))

After these steps, you can use time table column as axis, origin table column as value column create visuals with summarize original table values.

Regards,

Xiaoxin Sheng

[sokg]

Hello @demonfc

In order to determine the difference in hours you must have a start datetime and a end datetime.

In you date what is your start datetime and what is your end datetime???

[demonfc]

Hi sokg, 

Basically, it would be a 24 hour date/time of any given day. Starting at 12:00:00 AM of any given day and ending at 11:59:59 PM that same day. 

Thank you for your help.