Question description:
This user has given permission to use the problem statement for this
blog.
Each line following is a separate cell in a column.
Is there any way to extract the times (left numbers only) from these cells to calculate elapsed time.
11:59p-8a
9a-5p Mt/Sup
"4p-12a"
9a-5p Mt/Stk 1-8
"2p-10p"
9a-5p Mt/Escort 7-10
6a-5p A2/Stk Op 6-8
9a-5p Trn/Stk 5-2
Solved by E. H. in 29 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
26/08/2018 - 01:00
Hi
Excelchat Expert
26/08/2018 - 01:01
Welcome to Got IT Pro.
Excelchat Expert
26/08/2018 - 01:01
Please share the sample in the preview.
Excelchat Expert
26/08/2018 - 01:01
I would like to see what information from left you are looking for. It is difficult to read the text in the description.
Excelchat Expert
26/08/2018 - 01:03
Copy it from your local excel file to here.
Excelchat Expert
26/08/2018 - 01:05
What is the desired format in the column B?
User
26/08/2018 - 01:05
I wish to be able to calculate the elapsed time
User
26/08/2018 - 01:06
Col A only, can i extract the times to do this?
User
26/08/2018 - 01:07
Data is entered sometimes with quotes, and never with am or pm, on a or p.
Excelchat Expert
26/08/2018 - 01:07
I am sorry, my net was disconnected for a moment.
User
26/08/2018 - 01:08
I've tried using left, find (for the -) to find the times, but can be hour, or hour and min entries.
Excelchat Expert
26/08/2018 - 01:08
ok. a or p is used for am or pm?
User
26/08/2018 - 01:08
yes.
Excelchat Expert
26/08/2018 - 01:08
I am trying in the preview.
Excelchat Expert
26/08/2018 - 01:15
=timevalue(substitute(substitute(LEFT(B1,find("-",B1,1)-1),"a","AM"),"p","PM"))
Excelchat Expert
26/08/2018 - 01:15
It is in column C
Excelchat Expert
26/08/2018 - 01:15
=substitute(if(iferror(find(" ",A1,1),0)=0,A1,mid(A1,1,find(" ",A1,1))),"""","")
Excelchat Expert
26/08/2018 - 01:15
It is in column B.
Excelchat Expert
26/08/2018 - 01:15
Same way, you can get the right side time also.
Excelchat Expert
26/08/2018 - 01:17
=timevalue(substitute(substitute(RIGHT(B1,len(B1)-find("-",B1,1)),"a","AM"),"p","PM"))
Excelchat Expert
26/08/2018 - 01:17
It is for column D.
Excelchat Expert
26/08/2018 - 01:17
Hope, this is what you are looking for?
User
26/08/2018 - 01:18
awesome, how do i keep this to reference?
Excelchat Expert
26/08/2018 - 01:18
What reference?
Excelchat Expert
26/08/2018 - 01:18
I prefer to keep this in 3 stages. one to extract the column B and then times in next columns.
User
26/08/2018 - 01:19
sounds good. can I get a copy of the blank.xlsx so i can study it?
Excelchat Expert
26/08/2018 - 01:19
Please download the file.
Excelchat Expert
26/08/2018 - 01:19
File / Download as Excel.
Excelchat Expert
26/08/2018 - 01:19
Thank you.
Excelchat Expert
26/08/2018 - 01:20
Have wonderful day ahead.
User
26/08/2018 - 01:20
where do i download?
Excelchat Expert
26/08/2018 - 01:20
File menu.
Excelchat Expert
26/08/2018 - 01:20
Download as excel. It can be downloaded from preview.
User
26/08/2018 - 01:22
found it. thank you. perfect solution.
Excelchat Expert
26/08/2018 - 01:22
you are welcome.
Excelchat Expert
26/08/2018 - 01:22
Please end the session and leave a valuable feedback.
Excelchat Expert
26/08/2018 - 01:22
please do visit Got IT Pro.
User
26/08/2018 - 01:22
one last thing, format for col c & D? i get value error
Excelchat Expert
26/08/2018 - 01:23
I have formatted using hh:mm:ss AM/PM
Excelchat Expert
26/08/2018 - 01:24
Please remove timvalue in excel file.
Excelchat Expert
26/08/2018 - 01:25
It is not working. In Google sheets, it works.
User
26/08/2018 - 01:25
I see. I still have #value! error, hmmmm. Cols a & b are general
Excelchat Expert
26/08/2018 - 01:26
Ok. I am checking in excel. Please give me few minutes.
User
26/08/2018 - 01:27
no prob
Excelchat Expert
26/08/2018 - 01:27
.
[Uploaded an Excel file]
Excelchat Expert
26/08/2018 - 01:28
Please download file from here.
User
26/08/2018 - 01:29
Got it! And all seems good. I think I can handle it from here. Very good feedback coming...
Excelchat Expert
26/08/2018 - 01:29
Thank you.
Excelchat Expert
26/08/2018 - 01:29
Great to hear it.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
