Forum Discussion
Time difference HELP
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
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
3 Replies
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.
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
