Calculate Total minutes

S

solotech

New Member
Joined
Aug 5, 2018
Messages
7
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.


[TABLE="width: 576"]
<colgroup><col width="64" style="width:48pt" span="9"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: #C0C0C0"]Arrive: DATE[/TD]
[TD="width: 64, bgcolor: #C0C0C0"]NAME[/TD]
[TD="width: 64, bgcolor: #C0C0C0"]Gate[/TD]
[TD="width: 64, bgcolor: #C0C0C0"]Water meter[/TD]
[TD="width: 64, bgcolor: #C0C0C0"]Test [/TD]
[TD="width: 64, bgcolor: #C0C0C0"]Arrive: TIME[/TD]
[TD="width: 64, bgcolor: #C0C0C0"]Depart: DATE[/TD]
[TD="width: 64, bgcolor: #C0C0C0"]Depart: TIME[/TD]
[TD="width: 64, bgcolor: #C0C0C0"]TOTAL MINS[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]01.02.19[/TD]
[TD="bgcolor: transparent"]TEST 1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]003738[/TD]
[TD="bgcolor: transparent"]Staff[/TD]
[TD="bgcolor: transparent"]0415[/TD]
[TD="bgcolor: transparent"]01.02.19[/TD]
[TD="bgcolor: transparent"]1540[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
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]
 
Upvote 0
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. :-)
 
Upvote 0
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]
 
Upvote 0
How about PowerQuery?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Arrive: DATE[/td][td=bgcolor:#5B9BD5]NAME[/td][td=bgcolor:#5B9BD5]Gate[/td][td=bgcolor:#5B9BD5]Water meter[/td][td=bgcolor:#5B9BD5]Test [/td][td=bgcolor:#5B9BD5]Arrive: TIME[/td][td=bgcolor:#5B9BD5]Depart: DATE[/td][td=bgcolor:#5B9BD5]Depart: TIME[/td][td=bgcolor:#70AD47]Total Minutes[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]01.02.19[/td][td=bgcolor:#DDEBF7]TEST 1[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
3738​
[/td][td=bgcolor:#DDEBF7]Staff[/td][td=bgcolor:#DDEBF7]0415[/td][td=bgcolor:#DDEBF7]01.02.19[/td][td=bgcolor:#DDEBF7]
1540​
[/td][td=bgcolor:#E2EFDA]
685​
[/td][/tr]
[/table]

and what if Dept date is eg. 03.02.19 ?
 
Last edited:
Upvote 0
too late to edit

with different dates

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Arrive: DATE[/td][td=bgcolor:#5B9BD5]NAME[/td][td=bgcolor:#5B9BD5]Gate[/td][td=bgcolor:#5B9BD5]Water meter[/td][td=bgcolor:#5B9BD5]Test [/td][td=bgcolor:#5B9BD5]Arrive: TIME[/td][td=bgcolor:#5B9BD5]Depart: DATE[/td][td=bgcolor:#5B9BD5]Depart: TIME[/td][td=bgcolor:#70AD47]Total Minutes[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]01.02.19[/td][td=bgcolor:#DDEBF7]TEST 1[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
3738​
[/td][td=bgcolor:#DDEBF7]Staff[/td][td=bgcolor:#DDEBF7]0415[/td][td=bgcolor:#DDEBF7]03.02.19[/td][td=bgcolor:#DDEBF7]
1540​
[/td][td=bgcolor:#E2EFDA]
3565​
[/td][/tr]
[/table]
 
Upvote 0
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.

gaz_chops said:
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]
Click to expand...
 
Upvote 0
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
 
Upvote 0
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.

[TABLE="width: 614"]
<colgroup><col span="8"><col></colgroup><tbody>[TR]
[TD]21-01-19		TEST 11003738Staff041501-02-190515-62,576,597.781

<colgroup><col></colgroup><tbody>
</tbody>
[TABLE="width: 614"]
<colgroup><col span="8"><col></colgroup><tbody>[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]
<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
gaz_chops said:
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
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Calculating the number of hours between 2 dates/times
Replies
8
Views
612
Drexl88
D
L
  • Question Question
Formula to calculate total numbers of days Spent from Departure Date& Time and Arrival Date and time
Replies
3
Views
1K
Fluff
Fluff
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
M
  • Solved
Calculate total Days / Hours / Minutes between 2 dates , then average
Replies
8
Views
1K
Money#1
M
F
  • Question Question
Calculating Estimated Arrival Date & Time
Replies
1
Views
5K
steve the fish
steve the fish

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

Share this page

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