[FONT="]Hello All, [/FONT]
[FONT="] I have data that shows the when a rep enters a mall (In) and leaves a mall (Out) I would like to be able to calculate how long the rep spends within a mall in minutes. [/FONT]
[FONT="] [/FONT]
[FONT="]The data looks like this [/FONT]
<tbody style="box-sizing: inherit;">
</tbody>[FONT="] [/FONT]
[FONT="]The output I am after is something like this on the out status[/FONT]
[FONT="] [/FONT]
<tbody style="box-sizing: inherit;">
</tbody>[FONT="] [/FONT]
[FONT="]I am not sure if a calculated column is the best solution and have no idea where to start. Any help would be appreciated.
[/FONT]
[FONT="]Thank you,
Adam[/FONT]
[FONT="] I have data that shows the when a rep enters a mall (In) and leaves a mall (Out) I would like to be able to calculate how long the rep spends within a mall in minutes. [/FONT]
[FONT="] [/FONT]
[FONT="]The data looks like this [/FONT]
Rep | Location | Status | Date Time |
Rep 1 | Massey Mall | In | 2016/11/1 8:20 |
Rep 2 | Crown Mall | In | 2016/11/1 8:30 |
Rep 3 | Pacific Mall | In | 2016/11/1 8:40 |
Rep 2 | Crown Mall | Out | 2016/11/1 10:20 |
Rep 1 | Massey Mall | Out | 2016/11/1 11:00 |
Rep 3 | Pacific Mall | Out | 2016/11/1 12:00 |
Rep 2 | Crown Mall | In | 2016/11/2 8:00 |
Rep 1 | Massey Mall | In | 2016/11/2 8:20 |
Rep 3 | Pacific Mall | In | 2016/11/2 9:30 |
Rep 1 | Massey Mall | Out | 2016/11/2 10:00 |
Rep 3 | Pacific Mall | Out | 2016/11/2 10:00 |
Rep 2 | Crown Mall | Out | 2016/11/2 12:00 |
<tbody style="box-sizing: inherit;">
</tbody>
[FONT="]The output I am after is something like this on the out status[/FONT]
[FONT="] [/FONT]
Rep | Location | Status | Date Time | Time Spent In Mall (Minutes) |
Rep 1 | Massey Mall | In | 2016/11/1 8:20 | |
Rep 2 | Crown Mall | In | 2016/11/1 8:30 | |
Rep 3 | Pacific Mall | In | 2016/11/1 8:40 | |
Rep 2 | Crown Mall | Out | 2016/11/1 10:20 | 110 |
Rep 1 | Massey Mall | Out | 2016/11/1 11:00 | 160 |
Rep 3 | Pacific Mall | Out | 2016/11/1 12:00 | 200 |
Rep 2 | Crown Mall | In | 2016/11/2 8:00 | |
Rep 1 | Massey Mall | In | 2016/11/2 8:20 | |
Rep 3 | Pacific Mall | In | 2016/11/2 9:30 | |
Rep 1 | Massey Mall | Out | 2016/11/2 10:00 | 100 |
Rep 3 | Pacific Mall | Out | 2016/11/2 10:00 | 30 |
Rep 2 | Crown Mall | Out | 2016/11/2 12:00 | 240 |
<tbody style="box-sizing: inherit;">
</tbody>
[FONT="]I am not sure if a calculated column is the best solution and have no idea where to start. Any help would be appreciated.
[/FONT]
[FONT="]Thank you,
Adam[/FONT]