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

[owlf]

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!

ChatConversation	First Timestamp	Last 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 PM	10:46:58 PM

 

[sandy666]

try Power Query

Timestamp	Min	Max
Timestamp	22:45:20	22:46:58

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ETBD = Table.TransformColumns(Source, {{"ChatConversation", each Text.BeforeDelimiter(_, " -"), type text}}),
    Time = Table.AddColumn(ETBD, "Custom", each try Time.From(DateTimeZone.From([ChatConversation])) otherwise null),
    Filter = Table.SelectRows(Time, each ([Custom] <> null)),
    Label = Table.AddColumn(Filter, "Timestamp", each "Timestamp"),
    Group = Table.Group(Label, {"Timestamp"}, {{"Min", each List.Min([Custom]), type time}, {"Max", each List.Max([Custom]), type time}})
in
    Group

 

[sandy666]

or if whole text is in a single cell

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    SplitLF = Table.ExpandListColumn(Table.TransformColumns(Source, {{"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)),
    Timestamp = Table.AddColumn(Filter, "Timestamp", each "Timestamp"),
    Group = Table.Group(Timestamp, {"Timestamp"}, {{"Min", each List.Min([Time]), type time}, {"Max", each List.Max([Time]), type time}}),
    RC = Table.RemoveColumns(Group,{"Timestamp"})
in
    RC

ChatConversation	Min	Max
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?	22:45:20	22:46:58

 

[owlf]

try Power Query

Timestamp	Min	Max
Timestamp	22:45:20	22:46:58

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ETBD = Table.TransformColumns(Source, {{"ChatConversation", each Text.BeforeDelimiter(_, " -"), type text}}),
    Time = Table.AddColumn(ETBD, "Custom", each try Time.From(DateTimeZone.From([ChatConversation])) otherwise null),
    Filter = Table.SelectRows(Time, each ([Custom] <> null)),
    Label = Table.AddColumn(Filter, "Timestamp", each "Timestamp"),
    Group = Table.Group(Label, {"Timestamp"}, {{"Min", each List.Min([Custom]), type time}, {"Max", each List.Max([Custom]), type time}})
in
    Group

Thanks for the help...I'm not familiar with power query, how do I go about using this?

 

[sandy666]

Select your range with text and header
use Ctrl+T
from the ribbon : Data - From Table
you'll see Power Query Editor
from this ribbon open Advanced Editor
replace all code there with copied from the post
be sure the name of the table is the same as in the code
(be sure you use proper code from proper post)
ok
close&load

 

[sandy666]

more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

 

[owlf]

Select your range with text and header
use Ctrl+T
from the ribbon : Data - From Table
you'll see Power Query Editor
from this ribbon open Advanced Editor
replace all code there with copied from the post
be sure the name of the table is the same as in the code
(be sure you use proper code from proper post)
ok
close&load

Thanks! I think I sort of figured it out

Both formulas are only returning 1 result. The table has almost 1,000 rows of data. I am looking for a formula to get the start and end time in each row. Please let me know if I am doing something wrong. Thanks!

 

[sandy666]

Both formulas are only returning 1 result. The table has almost 1,000 rows of data. I am looking for a formula to get the start and end time in each row. Please let me know if I am doing something wrong.

I don't understand
You said and showed FirstTime and LastTime from the posted text so you got it
what else? Be more clear with your description

btw. this is M-code for Power Query not formula

 

[owlf]

I don't understand
You said and showed FirstTime and LastTime from the posted text so you got it
what else? Be more clear with your description

btw. this is M-code for Power Query not formula

Sorry for the confusion. What I posted was just one row of 900+ rows of data. I am looking for a formula that can do what you did for each and every row. So if there are 900 rows of data, then I would have 900 different start and end timestamps. Please let me know if that makes sense.

 

[sandy666]

so use post#3 , it will give you Min and Max time for each row
or
post a few more rows with expected result