Difference in military time - unformatted source data

[DG1215]

[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]

A	B	C
07JAN18 1100	08JAN18 1428	27:28
05JAN18 0530	05JAN18 1541	10:11
06JAN18 1310	08JAN18 1311	48:01

<colgroup><col><col><col></colgroup><tbody>
</tbody>

[FONT=&quot]
[/FONT]

 

[FormR]

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
	A	B	C
1	07JAN18 1100	08JAN18 1428	27:28:00
2	05JAN18 0530	05JAN18 1541	10:11:00
3	06JAN18 1310	08JAN18 1311	48:01:00
Sheet1

Cell Formulas
Range	Formula
C1	=REPLACE(B1,11,0,":")-REPLACE(A1,11,0,":")

 

[DG1215]

This works great. Thanks a lot!