If you copied and pasted it I can understand since there will be leading
blanks involved, I typed the info when testing, then my formula works,
otherwise use

=MOD(SUBSTITUTE(SUBSTITUTE(LEFT(TRIM(B1),6),"p"," PM"),"a","
AM")-SUBSTITUTE(SUBSTITUTE(LEFT(TRIM(A1),6),"p","PM")," a"," AM"),1)

(just tested it copied from the OP)

returns 04:02:00 formatted as hh:mm:ss





--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Trevor Shuttleworth" wrote in message
...
Peo

not sure how that would work as there's a load of other stuff in the cell.

I tried your formula with the data supplied and get #VALUE! Am I missing
something ?

My assumption is that cell A2 contains "10:35a Th Mar-23 JEF", not just
"10:35a"

Regards

Trevor


"Peo Sjoblom" wrote in message
...
If there is never more than 24 hours difference one could use

=MOD(SUBSTITUTE(SUBSTITUTE(LEFT(B1,6),"p"," PM"),"a","
AM")-SUBSTITUTE(SUBSTITUTE(LEFT(A1,6),"p","PM"),"a"," AM"),1)



--

Regards,

Peo Sjoblom


"Trevor Shuttleworth" wrote in message
...
Assuming your start time is in cell A2 and your end time is cell B2, in
cell C2 put the following formula:

=(DATEVALUE(MID(B2,15,2)&"-"&MID(B2,11,3))+IF(MID(B2,6,1)="a",TIME(LEFT(B2,2) ,MID(B2,4,2),0),TIME(LEFT(B2,2)+12,MID(B2,4,2),0)) )-(DATEVALUE(MID(A2,15,2)&"-"&MID(A2,11,3))+IF(MID(A2,6,1)="a",TIME(LEFT(A2,2) ,MID(A2,4,2),0),TIME(LEFT(A2,2)+12,MID(A2,4,2),0)) )

Format as [hh]:mm:ss

It also assumes that your data is in a standard format throughout

Regards

Trevor


"Jsb" wrote in message
...
I am importing Data from my call center as follows:
10:35a Th Mar-23 JEF 02:37p Th Mar-23 JEF
this data comes into column A and B and I want to calculate the elapse
time
in column C. I set up a custom format for each column "h:mm" and a
formula in
C as =b1-a1 but I am getting an error #value!. Help.