# Calculate time in minutes between two dates Dax/Power BI

##### New Member
[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]
 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=&quot] [/FONT]
[FONT=&quot]The output I am after is something like this on the out status[/FONT]
[FONT=&quot] [/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=&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,

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### Rummers

##### New Member

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

 A B C D E F 1 Rep Name Entered Here Location Date In Out Time (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.

##### Well-known Member
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

Replies
0
Views
82
Replies
1
Views
223
Replies
1
Views
69
Replies
0
Views
77
Replies
3
Views
171

1,191,587
Messages
5,987,510
Members
440,098
Latest member
MickyMouse123

### 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.

### Which adblocker are you using?

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

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