It wouldn't let me calculate the difference using your formula
came up as name error.
So I broke it all down individually for each step and came up with this combined formula (probably way too long version of what I want to achieve) which is initially how many hh:mm:ss between the two text dates from SAP that are just text.
Where cell C2 = Finish date and time
(as text not able to simply be formatted to date and time from menu)
Where cell B2 = Start date and time (as text not able to simply be formatted to date and time from menu)
In cell D2 = this formula to make new START date Excel format so I can subtract it from start date in cell f2
=DATE(RIGHT(B2,4),IF(MID(B2,5,3)="jan",1,IF(MID(B2,5,3)="feb",2,IF(MID(B2,5,3)="mar",3,IF(MID(B2,5,3)="apr",4,IF(MID(B2,5,3)="may",5,IF(MID(B2,5,3)="jun",6,IF(MID(B2,5,3)="jul",7,IF(MID(B2,5,3)="aug",8,IF(MID(B2,5,3)="sep",9,IF(MID(B2,5,3)="oct",10,IF(MID(B2,5,3)="nov",11,IF(MID(B2,5,3)="dec",12)))))))))))),DAY(MID(B2,9,2))))
+TIME(MID(B2,12,2),MID(B2,15,2),MID(B2,18,2))
In cell E2 = this formula to make new FINISH date Excel format so I can subtract it from start date in cell f2
=DATE(RIGHT(C2,4),IF(MID(C2,5,3)="jan",1,IF(MID(C2,5,3)="feb",2,IF(MID(C2,5,3)="mar",3,IF(MID(C2,5,3)="apr",4,IF(MID(C2,5,3)="may",5,IF(MID(C2,5,3)="jun",6,IF(MID(C2,5,3)="jul",7,IF(MID(C2,5,3)="aug",8,IF(MID(C2,5,3)="sep",9,IF(MID(C2,5,3)="oct",10,IF(MID(C2,5,3)="nov",11,IF(MID(C2,5,3)="dec",12)))))))))))),DAY(MID(C2,9,2)))+TIME(MID(C2,12,2),MID(C2,15,2),MID(C2,18,2))
In cell F2 - to calculate HH:MM:SS difference (number formatted)
=E2-D2
However I'm sure one of you has a compact version of what I did above to get Excel date and time format. But I figured it out eventually.