Calculate time in minutes between two dates Dax/Power BI

adamzee

New Member
Joined
Apr 12, 2015
Messages
9
[FONT=&quot]Hello All, [/FONT]
[FONT=&quot] 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=&quot] [/FONT]
[FONT=&quot]The data looks like this [/FONT]
RepLocationStatusDate Time
Rep 1Massey MallIn2016/11/1 8:20
Rep 2Crown MallIn2016/11/1 8:30
Rep 3Pacific MallIn2016/11/1 8:40
Rep 2Crown MallOut 2016/11/1 10:20
Rep 1Massey MallOut 2016/11/1 11:00
Rep 3Pacific MallOut 2016/11/1 12:00
Rep 2Crown MallIn2016/11/2 8:00
Rep 1Massey MallIn2016/11/2 8:20
Rep 3Pacific MallIn2016/11/2 9:30
Rep 1Massey MallOut2016/11/2 10:00
Rep 3Pacific MallOut2016/11/2 10:00
Rep 2Crown MallOut2016/11/2 12:00

<tbody style="box-sizing: inherit;">
</tbody>
[FONT=&quot] [/FONT]
[FONT=&quot]The output I am after is something like this on the out status[/FONT]
[FONT=&quot] [/FONT]
RepLocationStatusDate TimeTime Spent In Mall (Minutes)
Rep 1Massey MallIn2016/11/1 8:20
Rep 2Crown MallIn2016/11/1 8:30
Rep 3Pacific MallIn2016/11/1 8:40
Rep 2Crown MallOut 2016/11/1 10:20110
Rep 1Massey MallOut 2016/11/1 11:00160
Rep 3Pacific MallOut 2016/11/1 12:00200
Rep 2Crown MallIn2016/11/2 8:00
Rep 1Massey MallIn2016/11/2 8:20
Rep 3Pacific MallIn2016/11/2 9:30
Rep 1Massey MallOut2016/11/2 10:00100
Rep 3Pacific MallOut2016/11/2 10:0030
Rep 2Crown MallOut2016/11/2 12:00240

<tbody style="box-sizing: inherit;">
</tbody>
[FONT=&quot] [/FONT]
[FONT=&quot]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=&quot]Thank you,
Adam[/FONT]
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi Adam,

What you are trying to do will be rather complicated trying to reference Rep 1,2,3 and their In or Out status in the format you have given as an example above, mainly due to Rep1 being the first of the three in the first set of information (Row 1-3 'In') and then being second of the three in the order in the second set of information (Row 4-6 'Out').

If you are able to separate the reps and then have Date/In/Out columns it will be much easier to calculate. The calculation then becomes =sum(Out-In)x24x26

ABCDEF
1
Rep Name Entered HereLocationDateInOutTime (Mins)
2=A1 (Text Format)
Mall Name (Text Format)Date (Date Format)Time (HH:MM Format)Time (HH:MM Format)=SUM(E2-D2)*24*60

(Number Format, 0 Decimals)
3=A1 (Text Format)Mall Name (Text Format)Date (Date Format)Time (HH:MM Format)Time (HH:MM Format)=SUM(E3-D3)*24*60

(Number Format, 0 Decimals)
4=A1 (Text Format)Mall Name (Text Format)Date (Date Format)Time (HH:MM Format)Time (HH:MM Format)=SUM(E3-D3)*24*60

(Number Format, 0 Decimals)

<tbody>
</tbody>


Alternatively replace the formula in Column A with each reps name instead of having one sheet per rep.

hope this helps.
 
Upvote 0
If you would sort the lines by rep and location you can use something like

Excel Workbook
ABCDEF
1RepLocationStatusDate Time
2Rep 1Massey MallIn1-11-2016 08:20 
3Rep 1Massey MallOut1-11-2016 11:00160
4Rep 1Massey MallIn2-11-2016 08:20 
5Rep 1Massey MallOut2-11-2016 10:00100
6Rep 2Crown MallIn1-11-2016 08:30 
7Rep 2Crown MallOut1-11-2016 10:20110
8Rep 2Crown MallIn2-11-2016 08:00 
9Rep 2Crown MallOut2-11-2016 12:00240
10Rep 3Pacific MallIn1-11-2016 08:40 
11Rep 3Pacific MallOut1-11-2016 12:00200
12Rep 3Pacific MallIn2-11-2016 09:30 
13Rep 3Pacific MallOut2-11-2016 10:0030
Blad1
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top