# Calculate Total minutes

#### solotech

##### New Member
Hi,
I need to know the total minutes between ARRIVE DATE / ARRIVE TIME and DEPART DATE / DEPART TIME.
Times are in 24hr format. Can someone show me how to update TOTAL MINS as data is entered please.
Thank you.

 Arrive: DATE NAME Gate Water meter Test Arrive: TIME Depart: DATE Depart: TIME TOTAL MINS 01.02.19 TEST 1 1 003738 Staff 0415 01.02.19 1540

<colgroup><col width="64" style="width:48pt" span="9"> </colgroup><tbody>
</tbody>

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### gaz_chops

##### Well-known Member
Would be better if the date & times where entered as date & times!

=(SUBSTITUTE(G2,".","/")+TIME(LEFT(H2,LEN(H2)-2),RIGHT(H2,2),0))-(SUBSTITUTE(A2,".","/")+TIME(LEFT(F2,LEN(F2)-2),RIGHT(F2,2),0))

Code:
``````[TABLE="width: 783"]
<colgroup><col width="87" span="9" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 87"]Arrive: DATE[/TD]
[TD="class: xl64, width: 87"]NAME[/TD]
[TD="class: xl65, width: 87"]Gate[/TD]
[TD="class: xl66, width: 87"]Water meter[/TD]
[TD="class: xl67, width: 87"]Test [/TD]
[TD="class: xl68, width: 87"]Arrive: TIME[/TD]
[TD="class: xl66, width: 87"]Depart: DATE[/TD]
[TD="class: xl69, width: 87"]Depart: TIME[/TD]
[TD="class: xl69, width: 87"]TOTAL MINS[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]01.02.19[/TD]
[TD="class: xl71, bgcolor: transparent"]TEST 1[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]3738[/TD]
[TD="class: xl72, bgcolor: transparent"]Staff[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]415[/TD]
[TD="class: xl70, bgcolor: transparent"]01.02.19[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]1540[/TD]
[TD="class: xl74, align: right"]11:25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]``````

#### solotech

##### New Member
gaz, I'd love to change the format, but it's already set in stone and not by me.

Obviously, I'm a newbie. Just trying to add an auto-update to make life easier.

#### gaz_chops

##### Well-known Member

Change the cell format for "Total Mins", use a custom format [M]

Code:
``````[TABLE="width: 783"]
<colgroup><col width="87" span="9" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 87"]Arrive: DATE[/TD]
[TD="class: xl64, width: 87"]NAME[/TD]
[TD="class: xl65, width: 87"]Gate[/TD]
[TD="class: xl66, width: 87"]Water meter[/TD]
[TD="class: xl67, width: 87"]Test [/TD]
[TD="class: xl68, width: 87"]Arrive: TIME[/TD]
[TD="class: xl66, width: 87"]Depart: DATE[/TD]
[TD="class: xl69, width: 87"]Depart: TIME[/TD]
[TD="class: xl69, width: 87"]TOTAL MINS[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]01.02.19[/TD]
[TD="class: xl71, bgcolor: transparent"]TEST 1[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]3738[/TD]
[TD="class: xl72, bgcolor: transparent"]Staff[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]415[/TD]
[TD="class: xl70, bgcolor: transparent"]01.02.19[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]1540[/TD]
[TD="class: xl74, align: right"]685[/TD]
[/TR]
</tbody>[/TABLE]``````

#### sandy666

##### Banned - Rules violations

 Arrive: DATE NAME Gate Water meter Test Arrive: TIME Depart: DATE Depart: TIME Total Minutes 01.02.19 TEST 1 1​ 3738​ Staff 0415 01.02.19 1540​ 685​

and what if Dept date is eg. 03.02.19 ?

Last edited:

#### sandy666

##### Banned - Rules violations

too late to edit

with different dates

 Arrive: DATE NAME Gate Water meter Test Arrive: TIME Depart: DATE Depart: TIME Total Minutes 01.02.19 TEST 1 1​ 3738​ Staff 0415 03.02.19 1540​ 3565​

#### solotech

##### New Member
Thanks very much, gaz. I took your advice and changed dates and times to correct format.

Just one last thing I need to do. Minutes can end up being 5 digits long i.e. 12345 etc. Is there a way to add a comma delimiter to make it friendlier to read, as in 12,345? I tried using TEXT() and other formats, but no luck so far.

Thanks again.

Would be better if the date & times where entered as date & times!

=(SUBSTITUTE(G2,".","/")+TIME(LEFT(H2,LEN(H2)-2),RIGHT(H2,2),0))-(SUBSTITUTE(A2,".","/")+TIME(LEFT(F2,LEN(F2)-2),RIGHT(F2,2),0))

Code:
``````[TABLE="width: 783"]
<tbody>[TR]
[TD="class: xl63, width: 87"]Arrive: DATE[/TD]
[TD="class: xl64, width: 87"]NAME[/TD]
[TD="class: xl65, width: 87"]Gate[/TD]
[TD="class: xl66, width: 87"]Water meter[/TD]
[TD="class: xl67, width: 87"]Test[/TD]
[TD="class: xl68, width: 87"]Arrive: TIME[/TD]
[TD="class: xl66, width: 87"]Depart: DATE[/TD]
[TD="class: xl69, width: 87"]Depart: TIME[/TD]
[TD="class: xl69, width: 87"]TOTAL MINS[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]01.02.19[/TD]
[TD="class: xl71, bgcolor: transparent"]TEST 1[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]3738[/TD]
[TD="class: xl72, bgcolor: transparent"]Staff[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]415[/TD]
[TD="class: xl70, bgcolor: transparent"]01.02.19[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]1540[/TD]
[TD="class: xl74, align: right"]11:25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]``````

#### gaz_chops

##### Well-known Member
Good, much easier to work with

How are you now calculating the difference?

If you change the format for the cell "Total Mins" to number, then add *1440 to the end of your calculation, you will get e.g. 12,345

#### solotech

##### New Member
Weird. Changed total mins to Number and appended * 1440 to your code, but end up with a huge negative negative, instead of 15,900 minutes. Maybe time for a new past time.

 21-01-19 TEST 1 1 003738 Staff 0415 01-02-19 0515 -62,576,597.781

<colgroup><col span="8"><col></colgroup><tbody>
</tbody>

<colgroup><col span="8"><col></colgroup><tbody>
</tbody>
<strike></strike>

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
Good, much easier to work with

How are you now calculating the difference?

If you change the format for the cell "Total Mins" to number, then add *1440 to the end of your calculation, you will get e.g. 12,345

Replies
8
Views
152
Replies
1
Views
255
Replies
3
Views
237
Replies
5
Views
470
Replies
4
Views
410

1,129,754
Messages
5,638,170
Members
417,011
Latest member

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