Calculate Total minutes

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

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
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
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
Joined
Aug 5, 2018
Messages
7
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
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS

ADVERTISEMENT

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
Joined
Oct 24, 2015
Messages
7,499
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:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

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​
 

solotech

New Member
Joined
Aug 5, 2018
Messages
7
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
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
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
Joined
Aug 5, 2018
Messages
7
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>
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
 

Watch MrExcel Video

Forum statistics

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

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
Top