To answer our first question, “How much time did each user work per session?”, we will perform the following actions.
- Select the “End” column
- Press the CTRL (or Shift)
- Select the “Start” column
NOTES: We are selecting the “End” column first because we want our logic to read “End minus Start”. If we select the columns in the reverse order, we will end up with negative times.
- Select Add Column (tab) -> From Date & Time (group) -> Time -> Subtract.
NOTE: If the option for “Subtract” is not available (grayed-out), it is likely because the two columns of times were not selected. Return to Step 1 and try again.
The result is set as a Duration data type.
The Duration data type is represented as the length of time shown as days, hours, minutes, and seconds.
To display the result as a number of hours worked, select the results column and click Add Column (tab) -> From Date & Time (group) -> Duration -> Hours. The results are hours rounded down to the nearest hour.
If we were to select Add Column (tab) -> From Date & Time (group) -> Duration -> Total Hours, the result will be the precise number of hours (to the second.)
By using the Total Hours option, we can then round to the nearest hour in both directions by selecting the Total Hours column and clicking Transform (tab) -> Number Column (group) -> Rounding -> Round and select 1 as the decimal place value.
This gives us the results we need.