Sum of different timestamps in excell / word?

Microsoft Excel 2003 (full product)
May 10, 2010 at 07:44:40
Specs: Windows XP
Afternoon

I've got logs of various chats:

Conversation with bob at 1/3/2010
(6:23:57 PM) smith: xxx
(6:24:48 PM) smith: xxx
(6:24:51 PM) smith: xxx
(6:27:31 PM) bob: xxx

Conversation with bob at 5/4/2010
(12:31:25 PM) smith: xxx
(12:31:28 PM) bob: xxx
(12:31:37 PM) smith: xxx
(12:31:45 PM) bob: xxx

Conversation with bob at 5/7/2010
(10:21:25 PM) smith: xxx
(10:37:30 PM) bob: xxx
(10:38:54 PM) smith: xxx
(10:39:45 PM) bob: xxx

I now cant seem to find a formula or function that will somehow add up the hh:mm:ss spent on the chat and give me a total value?

The timestamps are not true excel timestamps for starters, and i've got so many 31 000 lines in excel so doing it bit by bit is out of the question.

Could someone please help me work out the formula so i can select A1:A31000 and it will tell me the total time I've spent chatting please?

Also some of the timestamps are backlogged so to say like here:

Conversation with bob at 2/2/2010
(1/3/2010 5:08:11 PM) bob: xxx
(9:53:57 PM) smith: xxx
(9:54:48 PM) smith: xxx
(9:54:51 PM) bob: xxx

so in the A column:
(1/3/2010 5:08:11 PM)

is going to come up instead of:
(9:53:57 PM)

as I only got the msg a day later.

Surely the excel SUMIF and COUNT function will be able to do all these calculations for me?

Thanks


Edit:
To make things even more complicated, I've just realised that the original chat logs are in .doc and .txt format, so in column A1 in excel, the first 10 characters are the time being (6:24:51 PM) then the rest of the line is the actual chat itself :/

Surely the there is a function that can read between bracket ( and bracket ) and register that as the time and do the calculations from there?
Or should I just try find some VBscript to read the .txt file and maybe try determine the sum of the value between the brackets?

Thanks


See More: Sum of different timestamps in excell / word?

Report •


#1
May 10, 2010 at 20:24:16
Before we can seriously attack the problem, we have to understand it a bit more.

When you show this "conversation"...

(6:23:57 PM) smith: xxx
(6:24:48 PM) smith: xxx
(6:24:51 PM) smith: xxx
(6:27:31 PM) bob: xxx

and say that you want to "add up the hh:mm:ss spent on the chat", what should the total for that session be?

Is it the total difference between 6:23:57 and 6:27:31 PMPM?

If so, something like this might work:

With that conversation in A1:A4, this formula will return 0:03:34

=TIMEVALUE(MID(A4,2,7))-TIMEVALUE(MID(A1,2,7))

The cell with the formula needs to be formatted as:

Custom h:mm:ss

The backlogged conversations have to be dealt with differently, but since I don't even know if my first suggestion is what you are looking for, I'll wait for your answer before I dig any deeper.



Report •

#2
May 11, 2010 at 04:11:55
I used the formula =LEFT(A13,9) to isolate the timestamp from each conversation to this " (6:24:51 "then used another formula to delete the " ( " from the beginning of each timestamp so basically managed to isolate the the exact timestamp in the form " 6:24:51 "

yeah, I pretty much need to work out the difference between A4 - A1 but the problem occurs that there alot of conversations so for conversation 1: I'll need A4 - A1 then for conversation 2: I'll need A28 - A6, conversation 3: A120 - A30
etc
Then at the end when I have all those values, I'll need the sum of all the the values from each of the conversations to work out the total timespent :/
Also dont know where each of the conversations start and begin as there are 31000 lines of chat, so sitting it and doing it manually is kinda out of the question :/

Is a bit of a nightmare to work out...

Any ideas? :D

Thanks Again


Report •

#3
May 11, 2010 at 07:16:55
What does your data actually look like?

Seeing what your really working with might help.

Post a portion.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
May 11, 2010 at 08:21:53
re: I used the formula =LEFT(A13,9) to isolate the timestamp...

First off, extracting the time can be done with one formula, as I showed in my suggestion:

=TIMEVALUE(MID(A4,2,7))

There's no need to use LEFT and then strip off the parenthesis.

MID will extract the 6:23:57 and TIMEVALUE will convert it to a time.

Second, once you have the times extracted, which cells you calculate the difference between is up to you.

If there is a pattern, such as every 4 cells, then every 10 cells then every 20 cells then constucting the formulas should be straight forward.

However, if there is no rhyme or reason to which cells will be used in the calculations then multiple steps (or maybe even VBA) will be needed to accomplish your task.

As Mike said, an extended sample of your actual data (maybe without the actual chat text) might help us offer something more.


Report •

#5
May 11, 2010 at 17:57:47
ok thanks DerbyDad. Your formula has helped alot so far.
Problem is, is that there is no pattern in the chat at all. Some conversations are alot longer than others.

Here is an example:

Conversation with amber at 1/27/2010 5:09:47 PM (mxit)
(5:09:48 PM) joe: hi
(5:09:31 PM) amber: :)
(5:10:05 PM) joe: where u now?
(5:09:55 PM) amber: On train
(5:10:27 PM) joe: where u wanna go for drinks?
(5:10:17 PM) amber: Bj
(5:10:48 PM) joe: hm...
(5:10:52 PM) joe: u wanna go home first?
(5:10:30 PM) amber: You really keen?
(5:10:33 PM) amber: Na
(5:10:58 PM) joe: or should i meet u there at bj now?
(5:10:39 PM) amber: Meet you there now
(5:11:15 PM) joe: u just gonna jump off at kennelworth station?
(5:11:14 PM) amber: Yup
(5:11:42 PM) joe: i'll leave now
(5:11:43 PM) joe: xx

Conversation with amber at 1/28/2010 7:26:15 PM (mxit)
(1/28/2010 4:44:42 PM) amber: blah
(7:26:19 PM) joe: bleh
(7:26:39 PM) joe: :D
(7:27:16 PM) amber: Hi
(7:28:03 PM) joe: hi
(7:28:19 PM) joe: how u
(7:27:58 PM) amber: Good and you
(7:28:02 PM) amber: Wana go to tiger?
(7:28:04 PM) amber: :):)
(7:29:04 PM) joe: lol
(7:29:08 PM) joe: who u going with?
(7:28:45 PM) amber: You :)
(7:29:37 PM) joe: lol
(7:29:57 PM) amber: You know you want to!
(7:31:21 PM) joe: ya
(7:31:33 PM) joe: people are coming to our place just now to play poker
(7:31:34 PM) joe: :/
(7:31:18 PM) amber: Na you don't wana do that
(7:32:32 PM) amber: :(
(7:33:16 PM) joe: dont even have money to play
(7:32:54 PM) amber: This is bot
(7:33:24 PM) joe: and our place is a mess from last night
(7:33:27 PM) joe: and im not even there
(7:33:31 PM) joe: only getting back at 8.30
(7:33:49 PM) joe: :/
(7:34:12 PM) joe: u bored at home?
(7:34:11 PM) amber: Yes :(
(7:35:32 PM) amber: :)
(7:35:46 PM) amber: I love it wen you a yes man
(7:39:30 PM) joe: hm...
(7:39:37 PM) joe: jack is keen as well
(7:39:31 PM) amber: Awesome
(7:41:57 PM) amber: I also broke we can have broke fun :)
(7:43:07 PM) joe: :/
(7:43:10 PM) joe: broke sober fun..
(7:43:12 PM) amber: Na it two for one
(7:48:19 PM) amber: Hi
(7:48:53 PM) joe: hi
(7:49:00 PM) joe: trying to think what im gonna do
(7:48:41 PM) amber: So wat your vaab
(7:48:43 PM) amber: Blah
(7:48:53 PM) amber: I need to tell lucy at 8 wat the story
(7:48:59 PM) amber: So you got til then :)
(7:49:35 PM) joe: ag
(7:49:40 PM) joe: lucy must just go with the flow
(7:49:51 PM) joe: for once in her life
(7:50:15 PM) joe: people are probably gonna wanna play poker for a bit
(7:50:22 PM) joe: and im gonna leave them there
(7:49:55 PM) amber: Ok
(7:50:46 PM) amber: Who all playing peter wana play when he get home
(7:51:15 PM) joe: *sigh*
(7:51:24 PM) joe: i donno whos playing
(7:51:30 PM) joe: gtg

Conversation with amber at 1/29/2010 5:22:25 PM (mxit)
(5:22:28 PM) joe: hey
(5:22:35 PM) joe: i'll buy your ticket for u this wkend
(5:22:37 PM) joe: :)
(5:22:45 PM) joe: will chat later tho
(5:22:47 PM) joe: xx


etc
etc

Really no method at all so I cannot identify the exact cells that would be the start or end of the conversation.
There is however a single blank line left open at the end of each conversation, so was thinking maybe a macro to pick this up and somehow use the time figure from the line before that and somehow subtracting the starting time?

Thanks guys


Report •

#6
May 11, 2010 at 18:33:03
What's with this:

Conversation with amber at 1/28/2010 7:26:15 PM (mxit)
(1/28/2010 4:44:42 PM) amber: blah
(7:26:19 PM)

How does the 4:44:42 fit into the situation?

Does the conversation start at 7:26:15?


Report •

#7
May 11, 2010 at 18:49:34
the conversation was backlogged there, as amber came online at 4:44:42, said something, then logged off before joe was online. joe only came only at 7:26:19 and then ambers message came up

Report •

#8
May 11, 2010 at 23:52:38
So what do you consider as the start time for backlogged conversations?

For the example with the 4:44:42, what answer would you be looking for?

Don't take this the wrong way, but we can't read minds. If you give us examples of the input (which you did, thanks) it helps if you also give us some outputs to help us understand what you are trying to accomplish.

For the examples you gave us in Response 5, what results would you expect the solution to return?


Report •

#9
May 12, 2010 at 06:06:05
ok sorry.

Basically I want to take the end time from the first conversation and subtract it from the beginning time of the first conversation
so basically:

(5:11:43 PM) - (5:09:48 PM) = 00:01:55

Then I want to take the end time from conversation 2 and subject it from the start time of conversation 2

eg:

(7:51:30 PM) - (7:26:19 PM) = 00:25:11

Same with conversation 3:

(5:22:47 PM) - (5:22:28 PM) =00:00:19


Then I want to take the values and add them:

00:01:55 + 00:25:11 + 00:00:19 = 00:27:25 (being the total time spent on chat)

The (1/28/2010 4:44:42 PM) from the backlogged chat is irrelevant as that date:

(1/28/2010

will just make life too difficult to work out whats happening.


So was thinking maybe a macro / script that can take the value underneath the heading (which is the start time of the conversation), the heading being:

"Conversation with amber at 1/27/2010 5:09:47 PM (mxit)

then the first time underneath the heading is the start time being (5:09:48 PM)
eg:

Conversation with amber at 1/27/2010 5:09:47 PM (mxit)
(5:09:48 PM) joe: hi


If however the macro detects a date instead of a time:

(1/28/2010 4:44:42 PM)

then the macro can ignore that entire conversation.


Ok, so after the start time is detected, then was hoping somehow that the macro can determine where the end of the conversation is (mainly determined by a blank open line after the last msg) then subtract the 2, get the time spent on chat, then proceed to do it to all the conversations and add up the total time spent on chat

bit of a headache....


Report •

#10
May 12, 2010 at 07:39:55
This code resulted in a total time of 00:27:25 for the example data given.

Notes:

1 - It assumes that the data is in Column A.
2 - It places the elasped time for each session in Column B.
3 - It SUMs the individual session times at the bottom of the list of individual session times.
4 - It clears Column B each time it runs to make room for next set of times.

All of these items can be adjusted, and there is no real need to place the individual session times in Column B; I did it merely to check the results against the times you gave in Response # 9.

Let me know what you think.

Option Explicit
Sub ChatTime()
Dim lstA_Rw, nxtB_Rw, timeStamp, chatSession As Integer
Dim startChat, endChat As String
'Clear old total from Column B
Range("B:B").ClearContents
'Find Last Row in Column A
 lstA_Rw = Range("A" & Rows.Count).End(xlUp).Row
'Loop through entries
   For timeStamp = 1 To lstA_Rw + 1
'If entry contains "Conversation" then Next cell is
'Start Time unless the "Time" is actually a "Date"
    If Cells(timeStamp, 1) Like "*Conversation*" Then
     startChat = Mid(Cells(timeStamp, 1).Offset(1, 0), 2, 7)
'If "Time" is a "Date" then Start Time is in *next* cell
      If startChat Like "*/*" Then
       startChat = Mid(Cells(timeStamp, 1).Offset(2, 0), 2, 7)
      End If
'Increment Row counter for Elasped time list
     chatSession = chatSession + 1
    End If
'If the entry is empty, then the End Time is in the cell above
    If Cells(timeStamp, 1) = "" Then
     endChat = Mid(Cells(timeStamp, 1).Offset(-1, 0), 2, 7)
'Put elasped time for session in Column B
     Cells(chatSession, 2) = TimeValue(endChat) - TimeValue(startChat)
    End If
   Next
''Sum the times for all Sessions
'Find Last Row in Column B
 nxtB_Rw = Range("B" & Rows.Count).End(xlUp).Row
  Cells(nxtB_Rw + 1, 2).Formula = "=SUM(B1:B" & nxtB_Rw & ")"
End Sub


Report •

#11
May 13, 2010 at 10:52:12
DerbyDad, you've taught me a lot so far. Your coding works unbelievably well, taking a lot of variables into account which I'm truly grateful for :] I've also been reading over your coding and teaching myself a couple things in excel which is pretty cool.

There one or 2 more variables that I've only discovered now, and I've been trying to do the coding myself but I've found myself going nowhere slowly :]

Firstly, some of the chat logs have more than 1 empty line between them

eg:


Conversation with amber at 1/29/2010 5:22:25 PM (mxit)
(5:22:28 PM) joe: listen
(5:22:35 PM) joe im coming over now
(5:22:37 PM) amber: :)
(5:22:45 PM) joe: cause wanna get it started
(5:22:47 PM) amber: :)


Conversation with caeli at 2/1/2010 4:33:11 PM (mxit)
(4:36:02 PM) joe: hi
(4:35:57 PM) caeli: Hi
(4:36:25 PM) amber: how u
(4:36:21 PM) joe: Busy you
(4:37:02 PM) amber: chilled
(4:37:12 PM) amber: 20.00?
(4:37:13 PM) joe: too early
(4:37:31 PM) amber: exactly wat i said

That gap between the 2 conversations is more than 1 line, so I get an Error 13 mismatch error, which I gather that the code doesnt know what to do when it gets to 2 or more empty lines other than just 1.

The 2nd variable occurs when some of the sentences were really long and proceed to go onto the next row in column A the chat will continue

eg:

Conversation with caeli at 1/3/2010 6:43:00 PM (mxit)
(6:43:00 PM) joe: how it go?
(6:43:26 PM) amber: Had one drink and dropped it into my bag. Was shaking :( and tom is here and annoying me i

gana punch him he just tuning me that i look like a zombie.
(6:44:20 PM) joe: so good night all in all :D
(6:44:26 PM) amber: ag whatever


So I think when its searching for the end time it tends to get stuck over there as its not a time or date but just some letters / words. Surely a simple addition to the code after here would fix it?

eg.

'If the entry is empty, then the End Time is in the cell above
If Cells(timeStamp, 1) = "" Then
endChat = Mid(Cells(timeStamp, 1).Offset(-1, 0), 2, 7)

If Cells(timeStamp, 1) = "TEXT" Then ignore
End If


yeah, I'm not exactly sure how to code but it would be something like that wouldnt it?

Thanks again DerbyDad


Report •

#12
May 14, 2010 at 18:40:34
re: If Cells(timeStamp, 1) = "TEXT" Then ignore

Nice try, but your suggested solution of looking for text won't work because every cell in your "conversation" contains text.

The only reason we can do a time calculation is because we are extracting a text string (e.g. "6:44:26") and using the TimeValue function to turn it into a time. Look up the TimeValue function in VBA Help to see what I mean.

That said, I think I fixed your issues…

Multiple Blank Lines

We know that a conversation starts after the word Conversation and that it ends just before the first blank line.

Once we have good startChat and endChat values and have calculated an elapsed time for a single conversation, we don’t need the startChat value anymore, so I set it to zero and use it as “flag”.

startChat = 0

I check that "flag" every time a blank line is found. If startChat = 0, then we haven't started a new conversation yet, so we can jump over the lines that set the endChat variable. That way 2 (or more) blank lines don’t cause endChat to get set to "" and cause a Type Mismatch error when TimeValue tries to use it.

In other words, we won’t set a new endChat string or try to calculate an elapsed time until the next startChat string has been set.

Wrapped Chat Lines

Now, as far as chat lines that wrap onto the next row, that should only be an issue if the last line of a conversation wraps. Since we only use the next one (or two) cells after the word "conversation" for startChat and the cell above the first blank line for endChat, wrapped lines in the middle of a session (like the one in your latest example) don't matter.

However, if the wrapped line is the line (or lines) above the blank line, then it is an issue since the code will try to use it to set the endChat variable. Unfortunately, we can't simply ignore those lines because we still need to determine the end time of that conversation.

To deal with that issue, I added an Error Handler and one additional variable.

Originally, the Offset function used -1 as the Row argument to look one Row above the blank line for something to build the endChat string with.

It now uses a variable called upRow so we can tell the code how many Rows the Offset function should use.

The first time the code sees a blank line, upRow = -1 and the Offset looks one row up. If endChat gets sets to something that TimeValue can use, then all is good.

However, if a chat line has wrapped, then endChat might look like “gana pu” which will cause the TimeValue to raise a Type Mismatch error.

Once the error is raised, the code will jumped to the Error Handler routine (errWrappedLine) which will "decrement" the upRow variable and then Resume back at the line that pulls the endChat string.

For example, if upRow now equals -2, then the Offset function will look two rows above the blank line and set endChat again. The Error Handler will repeat this (decrementing upRow) as many times as it takes until TimeValue stops raising the error and we’ve found a valid endChat time.

I hope that makes sense!

Anyway, here’s the new code.

Wait…let me say right here that I’m guessing you are going to find other anomalies in your conversations. There are only so many unique situations we can deal with before it gets to the point where adding more code to handle each one-off issue gets to be more cumbersome than it’s worth – especially when someone is giving up their free time to write the code for you. I’m just sayin’….

Option Explicit
Sub ChatTime()
Dim lstA_Rw, nxtB_Rw, timeStamp, chatSession, upRow As Integer
Dim startChat, endChat As String
On Error GoTo errWrappedLine
'Clear old total from Column B
 Range("B:B").ClearContents
'Find Last Row in Column A
  lstA_Rw = Range("A" & Rows.Count).End(xlUp).Row
'Loop through entries
    For timeStamp = 1 To lstA_Rw + 1
'If entry contains "Conversation" then Next cell is
'Start Time unless the "Time" is actually a "Date"
    If Cells(timeStamp, 1) Like "*Conversation*" Then
     startChat = Mid(Cells(timeStamp, 1).Offset(1, 0), 2, 7)
'If "Time" is a "Date" then Start Time is in *next* cell
     If startChat Like "*/*" Then
      startChat = Mid(Cells(timeStamp, 1).Offset(2, 0), 2, 7)
     End If
'Increment Row counter for Elasped time list
     chatSession = chatSession + 1
    End If
'If the entry is empty, then the End Time is in the cell above
    If Cells(timeStamp, 1) = "" Then
'If startChat = 0, we haven't started a new Conversation yet
     If startChat = 0 Then GoTo TwoBlanks
'Set variable for Row argument of Offset function
       upRow = -1
WrappedLineReturn:
'Set endChat String
       endChat = Mid(Cells(timeStamp, 1).Offset(upRow, 0), 2, 7)
'Put elasped time for session in Column B
      Cells(chatSession, 2) = TimeValue(endChat) - TimeValue(startChat)
'Clear startChat to use as TwoBlanks Flag
      startChat = 0
    End If
TwoBlanks:
    Next
''Sum the times for all Sessions
'Find Last Row in Column B
  nxtB_Rw = Range("B" & Rows.Count).End(xlUp).Row
  Cells(nxtB_Rw + 1, 2).Formula = "=SUM(B1:B" & nxtB_Rw & ")"
'We're Done!
  Exit Sub
'Error handler for Wrapped Line
errWrappedLine:
'Decrement Row Argument for Offset function
  upRow = upRow - 1
'Try a new endChat time
  Resume WrappedLineReturn
End Sub


Report •

#13
May 16, 2010 at 08:53:22
DerbyDad, thank you very much for your time and coding skills. You've helped me out a great deal and it is truly appreciated :] Have learnt alot from your work and the coding works perfectly. Managed to calculate the entire time spent with this chat program.
Thanks again for helping me out and spending a lot of your time working on the code then on the variables afterwards :]



Report •

#14
May 16, 2010 at 09:37:04
You're welcome...but just wait until a conversation spans midnight...you'll be back.

Report •

Ask Question