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.


Arrive: DATENAMEGateWater meterTest Arrive: TIMEDepart: DATEDepart: TIMETOTAL MINS
01.02.19TEST 11003738Staff041501.02.191540

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

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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?

Arrive: DATENAMEGateWater meterTest Arrive: TIMEDepart: DATEDepart: TIMETotal Minutes
01.02.19TEST 1
1​
3738​
Staff041501.02.19
1540​
685​

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

with different dates

Arrive: DATENAMEGateWater meterTest Arrive: TIMEDepart: DATEDepart: TIMETotal Minutes
01.02.19TEST 1
1​
3738​
Staff041503.02.19
1540​
3565​
 
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.

21-01-19TEST 11003738Staff041501-02-190515-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>
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
Calculating the number of hours between 2 dates/times
Replies
8
Views
521
Drexl88
D
L
  • 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
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
985
Money#1
M
F
  • Question
Calculating Estimated Arrival Date & Time
Replies
1
Views
5K
steve the fish
steve the fish

Forum statistics

Threads
1,214,588
Messages
6,120,412
Members
448,959
Latest member
camelliaCase

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