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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
try Power Query
TimestampMinMax
Timestamp22:45:2022:46:58

Rich (BB code):
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
 
Upvote 0
or if whole text is in a single cell
Rich (BB code):
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
ChatConversationMinMax
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:2022:46:58
 
Last edited:
Upvote 0
try Power Query
TimestampMinMax
Timestamp22:45:2022:46:58

Rich (BB code):
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?
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
so use post#3 , it will give you Min and Max time for each row
or
post a few more rows with expected result
 
Upvote 0

Forum statistics

Threads
1,224,586
Messages
6,179,710
Members
452,939
Latest member
WCrawford

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