Difference in military time - unformatted source data

DG1215

New Member
Joined
Jan 11, 2018
Messages
2
[FONT=&quot]I have a set of source data, where the time in a given cell reads like this: “07JAN18 1100”[/FONT][FONT=&quot]. I am trying to come up with a formula that will give me the difference between 2 of these times in [H]:MM.[/FONT][FONT=&quot] Please see examples below. I am trying to achieve column C with a formula. Columns A and B are the source data.[/FONT]

[FONT=&quot]
I would like to be able to achieve this in as few steps as possible. I am aware that I might have to use text to columns to break up the source data, but an automated formula is preferred as I will be importing the data several times a week for the foreseeable future. Any input is greatly appreciated!

[/FONT][TABLE="width: 335"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]07JAN18 1100[/TD]
[TD]08JAN18 1428
[/TD]
[TD]27:28
[/TD]
[/TR]
[TR]
[TD]05JAN18 0530[/TD]
[TD]05JAN18 1541
[/TD]
[TD]10:11
[/TD]
[/TR]
[TR]
[TD]06JAN18 1310[/TD]
[TD]08JAN18 1311[/TD]
[TD]48:01
[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot]
[/FONT]
 
Hi, welcome to the forum!

Assuming the values are text, you could try this. Note: you will need to custom format the cells that contain the formula as [hh]:mm:ss


Excel 2013/2016
ABC
107JAN18 110008JAN18 142827:28:00
205JAN18 053005JAN18 154110:11:00
306JAN18 131008JAN18 131148:01:00
Sheet1
Cell Formulas
RangeFormula
C1=REPLACE(B1,11,0,":")-REPLACE(A1,11,0,":")
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,926
Members
453,767
Latest member
922aloose

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