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.

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.

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.

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.

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.

Peo

yes, you're right ... looks as though I just put your formula on the wrong
row (C2 instead of C1)

The advantage of my formula is that it takes the date into account too so it
will cater for periods starting one day and finishing in the next, if that
could happen.

Regards

Trevor


"Peo Sjoblom" wrote in message
...
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.

Actually as long as there less than 24 hours my formula will work regardless
if the start date is different, that is what the MOD part takes care of, if
24 + than my formula will not work

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Trevor Shuttleworth" wrote in message
...
Peo

yes, you're right ... looks as though I just put your formula on the wrong
row (C2 instead of C1)

The advantage of my formula is that it takes the date into account too so
it will cater for periods starting one day and finishing in the next, if
that could happen.

Regards

Trevor


"Peo Sjoblom" wrote in message
...
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.

c'est la vie ;-)


"Peo Sjoblom" wrote in message
...
Actually as long as there less than 24 hours my formula will work
regardless if the start date is different, that is what the MOD part takes
care of, if 24 + than my formula will not work

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Trevor Shuttleworth" wrote in message
...
Peo

yes, you're right ... looks as though I just put your formula on the
wrong row (C2 instead of C1)

The advantage of my formula is that it takes the date into account too so
it will cater for periods starting one day and finishing in the next, if
that could happen.

Regards

Trevor


"Peo Sjoblom" wrote in message
...
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.

Given your sample data, this formula seemed to work:

=((DATEVALUE(MID(B1, 11, 6)) + TIMEVALUE(LEFT(B1,5) & " " & MID(B1, 6, 1)))
- (DATEVALUE(MID(A1, 11, 6)) + TIMEVALUE(LEFT(A1,5) & " " & MID(A1, 6,1))))

--
Kevin Vaughn


"Jsb" wrote:

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.