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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>>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" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>>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" <[email protected]> wrote in message
news:udrfTQ%[email protected]...
> 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" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message
> news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
>>> news:[email protected]...
>>>> 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" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> 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" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>>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" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:udrfTQ%[email protected]...
>> 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" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message
>> news:%[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
>>>> news:[email protected]...
>>>>> 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" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>> 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" <[email protected]> wrote in message
>>>>>> news:[email protected]...
>>>>>>>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.
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks