Formatting imported data

[Jsb]

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.

 

[Trevor Shuttleworth]

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

 

[Peo Sjoblom]

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

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

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.

 

[Trevor Shuttleworth]

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

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

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

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

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

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

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

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


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.

 

[Harlan Grove]

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.

If the format is fixed and always has the same number of characters,
weekdays are always 2 characters, year is never included, and start and
end times could be different sides of midnight but would never span
more than 24 hours, try

=MOD((REPLACE(LEFT(TRIM(E3),6),6,0," ")&"m")
-(REPLACE(LEFT(TRIM(E2),6),6,0," ")&"m"),1)

 

[Trevor Shuttleworth]

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

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

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

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


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


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

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

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

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

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


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


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


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.

 

[Trevor Shuttleworth]

c'est la vie ;-)

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

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

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




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


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


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


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.

 

[Kevin Vaughn]

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))))