[FONT="]I have a set of source data, where the time in a given cell reads like this: “07JAN18 1100”[/FONT][FONT="]. 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="] Please see examples below. I am trying to achieve column C with a formula. Columns A and B are the source data.[/FONT]
[FONT="]
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="]
[/FONT]
[FONT="]
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="]
[/FONT]