Get the first and last occurrence of a timestamp from a cell

owlf

New Member
Joined
May 18, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I have an Excel file that contains the chatbot log from a website. Instead a cell in each row, the full chat conversation is saved. What I am looking to do is extract the first timestamp that appears as well as the last one. Each chatbot conversation can have different amounts of timestamps in the cell. Sometimes a few and at other times 20+. I am only interested in the first and last occurrence.

Ideally, I'd like a formula to get this info and not VBA.

There can be two formulas if needed to get the first and last timestamps into their own cells. In the chart below, the first column is the chat conversation and the 2nd and 3rd column is where I would like to place the timestamps. Let me know if you have any questions. Thanks in advance!

ChatConversationFirst TimestampLast Timestamp
03/11/2020 10:45:20 PM - CHATBOT
Hi there! My name is CHATBOT. I’m a bot, here to help you.
Can I ask for your name?
03/11/2020 10:45:24 PM - USER
Hi, can you please help me?
03/11/2020 10:45:26 PM - CHATBOT
Please type out your full first and last name without the use of initials.
Can I ask for your name?
03/11/2020 10:45:40 PM - USER
John Doe
03/11/2020 10:45:41 PM - CHATBOT
Thanks, John Doe!
Can I also get your email in case we get disconnected?
03/11/2020 10:46:24 PM - USER
test@test.com
03/11/2020 10:46:26 PM - CHATBOT
Wonderful! Let’s get started.
Do you want to schedule time with someone who can tell you more about our services?
Yes
No
03/11/2020 10:46:35 PM - USER
Yes
03/11/2020 10:46:37 PM - CHATBOT
Excellent, let’s schedule a meeting!
I just need to collect a little information about your company first.
What’s the name of your company?
03/11/2020 10:46:45 PM - USER
ABC
03/11/2020 10:46:46 PM - CHATBOT
Got it!
How many employees do you have at your company?
03/11/2020 10:46:56 PM - USER
100+
03/11/2020 10:46:58 PM - CHATBOT
Thanks for sharing!
What aspects of our services prompted your interest in us?

10:45:20 PM10:46:58 PM
 
so use post#3 , it will give you Min and Max time for each row
or
post a few more rows with expected result

Sure thing, here are 3 more. Let me know if this is what you needed.

ChatConversationFirst TimestampLast Timestamp
03/10/2020 3:45:05 PM - CHATBOT
Hi there! My name is CHATBOT. I’m a bot, here to help you.
Can I ask for your name?
03/10/2020 3:45:13 PM - USER
Janette
03/10/2020 3:45:14 PM - CHATBOT
Thanks, Janette!
Can I also get your email in case we get disconnected?
03/10/2020 3:45:28 PM - USER
test@test.com
03/10/2020 3:45:29 PM - CHATBOT
Wonderful! Let’s get started.
Do you want to schedule time with someone who can tell you more about our services?
Yes
No
03/10/2020 3:45:42 PM - USER
No
03/10/2020 3:45:43 PM - CHATBOT
I understand that you might not be ready to speak to someone yet.
Can I help you with something else?
Schedule an appointment
Learn more
Speak to a live person
Customer service
Career opportunities
3:45:05 PM3:45:43 PM
03/10/2020 8:05:05 AM - CHATBOT
Hi there! My name is CHATBOT. I’m a bot, here to help you.
Can I ask for your name?
03/10/2020 8:05:10 AM - USER
Mark
03/10/2020 8:05:11 AM - CHATBOT
Thanks, Mark!
Can I also get your email in case we get disconnected?
03/10/2020 8:05:24 AM - USER
test@test.com
03/10/2020 8:05:25 AM - CHATBOT
Awesome!
Are you ready to schedule time with someone who can tell you more about our services?
Yes
No
8:05:05 AM8:05:25 AM
03/10/2020 6:24:58 PM - CHATBOT
Hi there! My name is CHATBOT. I’m a bot, here to help you.
Can I ask for your name?
03/10/2020 6:25:04 PM - USER
Goutham
03/10/2020 6:25:05 PM - CHATBOT
Nice to meet you, Goutham!
Can I also get your email in case we get disconnected?
03/10/2020 6:25:23 PM - USER
test@test.com
03/10/2020 6:25:25 PM - CHATBOT
Wonderful! Let’s get started.
Do you want to schedule time with someone who can tell you more about our services?
Yes
No
03/10/2020 6:25:32 PM - USER
No
03/10/2020 6:25:34 PM - CHATBOT
I understand that you might not be ready to speak to someone yet.
Can I help you with something else?
Schedule an appointment
Learn more
Speak to a live person
Customer service
Career opportunities
03/10/2020 6:25:49 PM - USER
Speak to a live person
03/10/2020 6:25:50 PM - CHATBOT
If you’d like to talk to a live person, feel free to call us at 888-888-8888 from 8 a.m. to 6 p.m. CT on Monday – Friday.
Is there anything else I can help you with today?
6:24:58 PM6:25:50 PM
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
is that what you want?

ChatConversationMinMax
03/10/2020 3:45:05 PM - CHATBOT Hi there! My name is CHATBOT. I’m a bot, here to help you. Can I ask for your name? 03/10/2020 3:45:13 PM - USER Janette 03/10/2020 3:45:14 PM - CHATBOT Thanks, Janette! Can I also get your email in case we get disconnected? 03/10/2020 3:45:28 PM - USER test@test.com 03/10/2020 3:45:29 PM - CHATBOT Wonderful! Let’s get started. Do you want to schedule time with someone who can tell you more about our services? Yes No 03/10/2020 3:45:42 PM - USER No 03/10/2020 3:45:43 PM - CHATBOT I understand that you might not be ready to speak to someone yet. Can I help you with something else? Schedule an appointment Learn more Speak to a live person Customer service Career opportunities Hi there! My name is CHATBOT. I’m a bot, here to help you. Can I ask for your name? 03/11/2020 10:45:24 PM - USER Hi, can you please help me? 03/11/2020 10:45:26 PM - CHATBOT Please type out your full first and last name without the use of initials. Can I ask for your name? 03/11/2020 10:45:40 PM - USER John Doe 03/11/2020 10:45:41 PM - CHATBOT Thanks, John Doe! Can I also get your email in case we get disconnected? 03/11/2020 10:46:24 PM - USER test@test.com 03/11/2020 10:46:26 PM - CHATBOT Wonderful! Let’s get started. Do you want to schedule time with someone who can tell you more about our services? Yes No 03/11/2020 10:46:35 PM - USER Yes 03/11/2020 10:46:37 PM - CHATBOT Excellent, let’s schedule a meeting! I just need to collect a little information about your company first. What’s the name of your company? 03/11/2020 10:46:45 PM - USER ABC 03/11/2020 10:46:46 PM - CHATBOT Got it! How many employees do you have at your company? 03/11/2020 10:46:56 PM - USER 100+ 03/11/2020 10:46:58 PM - CHATBOT Thanks for sharing! What aspects of our services prompted your interest in us?15:45:0522:46:58
03/10/2020 8:05:05 AM - CHATBOT Hi there! My name is CHATBOT. I’m a bot, here to help you. Can I ask for your name? 03/10/2020 8:05:10 AM - USER Mark 03/10/2020 8:05:11 AM - CHATBOT Thanks, Mark! Can I also get your email in case we get disconnected? 03/10/2020 8:05:24 AM - USER test@test.com 03/10/2020 8:05:25 AM - CHATBOT Awesome! Are you ready to schedule time with someone who can tell you more about our services? Yes No08:05:0508:05:25
03/10/2020 6:24:58 PM - CHATBOT Hi there! My name is CHATBOT. I’m a bot, here to help you. Can I ask for your name? 03/10/2020 6:25:04 PM - USER Goutham 03/10/2020 6:25:05 PM - CHATBOT Nice to meet you, Goutham! Can I also get your email in case we get disconnected? 03/10/2020 6:25:23 PM - USER test@test.com 03/10/2020 6:25:25 PM - CHATBOT Wonderful! Let’s get started. Do you want to schedule time with someone who can tell you more about our services? Yes No 03/10/2020 6:25:32 PM - USER No 03/10/2020 6:25:34 PM - CHATBOT I understand that you might not be ready to speak to someone yet. Can I help you with something else? Schedule an appointment Learn more Speak to a live person Customer service Career opportunities 03/10/2020 6:25:49 PM - USER Speak to a live person 03/10/2020 6:25:50 PM - CHATBOT If you’d like to talk to a live person, feel free to call us at 888-888-8888 from 8 a.m. to 6 p.m. CT on Monday – Friday. Is there anything else I can help you with today?18:24:5818:25:50


Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1),
    SplitLF = Table.ExpandListColumn(Table.TransformColumns(Index, {{"ChatConversation", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ChatConversation"),
    ETBD = Table.TransformColumns(SplitLF, {{"ChatConversation", each Text.BeforeDelimiter(_, " -"), type text}}),
    Time = Table.AddColumn(ETBD, "Time", each try Time.From(DateTimeZone.From([ChatConversation])) otherwise null),
    Filter = Table.SelectRows(Time, each ([Time] <> null)),
    Group = Table.Group(Filter, {"Index"}, {{"Min", each List.Min([Time]), type time}, {"Max", each List.Max([Time]), type time}}),
    RC = Table.RemoveColumns(Group,{"Index"})
in
    RC
 
Upvote 0
is that what you want?

ChatConversationMinMax
03/10/2020 3:45:05 PM - CHATBOT Hi there! My name is CHATBOT. I’m a bot, here to help you. Can I ask for your name? 03/10/2020 3:45:13 PM - USER Janette 03/10/2020 3:45:14 PM - CHATBOT Thanks, Janette! Can I also get your email in case we get disconnected? 03/10/2020 3:45:28 PM - USER test@test.com 03/10/2020 3:45:29 PM - CHATBOT Wonderful! Let’s get started. Do you want to schedule time with someone who can tell you more about our services? Yes No 03/10/2020 3:45:42 PM - USER No 03/10/2020 3:45:43 PM - CHATBOT I understand that you might not be ready to speak to someone yet. Can I help you with something else? Schedule an appointment Learn more Speak to a live person Customer service Career opportunities Hi there! My name is CHATBOT. I’m a bot, here to help you. Can I ask for your name? 03/11/2020 10:45:24 PM - USER Hi, can you please help me? 03/11/2020 10:45:26 PM - CHATBOT Please type out your full first and last name without the use of initials. Can I ask for your name? 03/11/2020 10:45:40 PM - USER John Doe 03/11/2020 10:45:41 PM - CHATBOT Thanks, John Doe! Can I also get your email in case we get disconnected? 03/11/2020 10:46:24 PM - USER test@test.com 03/11/2020 10:46:26 PM - CHATBOT Wonderful! Let’s get started. Do you want to schedule time with someone who can tell you more about our services? Yes No 03/11/2020 10:46:35 PM - USER Yes 03/11/2020 10:46:37 PM - CHATBOT Excellent, let’s schedule a meeting! I just need to collect a little information about your company first. What’s the name of your company? 03/11/2020 10:46:45 PM - USER ABC 03/11/2020 10:46:46 PM - CHATBOT Got it! How many employees do you have at your company? 03/11/2020 10:46:56 PM - USER 100+ 03/11/2020 10:46:58 PM - CHATBOT Thanks for sharing! What aspects of our services prompted your interest in us?15:45:0522:46:58
03/10/2020 8:05:05 AM - CHATBOT Hi there! My name is CHATBOT. I’m a bot, here to help you. Can I ask for your name? 03/10/2020 8:05:10 AM - USER Mark 03/10/2020 8:05:11 AM - CHATBOT Thanks, Mark! Can I also get your email in case we get disconnected? 03/10/2020 8:05:24 AM - USER test@test.com 03/10/2020 8:05:25 AM - CHATBOT Awesome! Are you ready to schedule time with someone who can tell you more about our services? Yes No08:05:0508:05:25
03/10/2020 6:24:58 PM - CHATBOT Hi there! My name is CHATBOT. I’m a bot, here to help you. Can I ask for your name? 03/10/2020 6:25:04 PM - USER Goutham 03/10/2020 6:25:05 PM - CHATBOT Nice to meet you, Goutham! Can I also get your email in case we get disconnected? 03/10/2020 6:25:23 PM - USER test@test.com 03/10/2020 6:25:25 PM - CHATBOT Wonderful! Let’s get started. Do you want to schedule time with someone who can tell you more about our services? Yes No 03/10/2020 6:25:32 PM - USER No 03/10/2020 6:25:34 PM - CHATBOT I understand that you might not be ready to speak to someone yet. Can I help you with something else? Schedule an appointment Learn more Speak to a live person Customer service Career opportunities 03/10/2020 6:25:49 PM - USER Speak to a live person 03/10/2020 6:25:50 PM - CHATBOT If you’d like to talk to a live person, feel free to call us at 888-888-8888 from 8 a.m. to 6 p.m. CT on Monday – Friday. Is there anything else I can help you with today?18:24:5818:25:50


Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1),
    SplitLF = Table.ExpandListColumn(Table.TransformColumns(Index, {{"ChatConversation", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ChatConversation"),
    ETBD = Table.TransformColumns(SplitLF, {{"ChatConversation", each Text.BeforeDelimiter(_, " -"), type text}}),
    Time = Table.AddColumn(ETBD, "Time", each try Time.From(DateTimeZone.From([ChatConversation])) otherwise null),
    Filter = Table.SelectRows(Time, each ([Time] <> null)),
    Group = Table.Group(Filter, {"Index"}, {{"Min", each List.Min([Time]), type time}, {"Max", each List.Max([Time]), type time}}),
    RC = Table.RemoveColumns(Group,{"Index"})
in
    RC

Yes, that looks like it works!

One more question...is there a way to easily add this data as two new columns to the table it is getting the data from? Right now, it creates a new workbook with the data.
 
Upvote 0
I think worksheet not workbook :)
sure, delete this new worksheet then select cell where you want the result table (top left corner) then Data - Show Queries then right click on the table in Workbook Queries and select Load To..., choose Table and Existing Worksheet
 
Upvote 0
I think worksheet not workbook :)
sure, delete this new worksheet then select cell where you want the result table (top left corner) then Data - Show Queries then right click on the table in Workbook Queries and select Load To..., choose Table and Existing Worksheet

Yes, worksheet :)

I'm having trouble finding "Data - Show Queries", is this in the data tab/ribbon, or inside the Power Query Editor?
 
Upvote 0
Data tab on the basic ribbon
sq.png

maybe Queries and Connections but I am not a follower of 365 :)
 
Upvote 0
sq.png

if you click this you should see on the right side Queries / Connections pane

or try Alt+A+PS
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top