Calculate online usage and rest times 1

[redjackal]

I have a data set containing internet usage. There are over 9000 records spanning more than a month. The columns show time and web site visited.

I'm trying to show how much time the person spent online. How much time they spent without taking a break of 15 minutes or more. I would also like to make a chart of some king to show the increase or decrease per day of time spent online and also the increase or decrease per day of time spent away from the computer.

I have already figured out how to calculate the elapsed time between each individual site visit.

here is a sample of the data that I have:

Date elapsed site
8/8/2007 1:34 0:15:20 facebook.com/123456
8/8/2007 1:34 0:00:03 facebook.com/654321
8/8/2007 4:23 2:48:25 google.com
8/8/2007 4:23 0:00:19 cnn.com

 

[SkipVought]

Hi,

You don't have any example of time away from the computer.

How do you want to summarize: just ON LINE and NOT? If so, each of the rows you posted above would be categorized as ON LINE. You would simply aggregate, probably using a PivotTable, ON LINE/NOT column, across DATE, and SUM elapsed. Pretty simple!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[redjackal]

Thanks for the reply!

online or not is an assumed number based on an elapsed time of 15 minutes or greater.

what I'm trying to show is the amount of time that the person spent going from site to site over a months period of time. we want to identify a pattern or trend in rest and perhaps sleep deprivation or "frantic" clicking from site to site. with 9000+ records, it has not been easy to get an overview or some kind of line chart.

I have attached the data if you wanted to take a look.

 

[SkipVought]

online or not is an assumed number based on an elapsed time of 15 minutes or greater.

HUH? Do you mean if you have an elapsed time >= 15 min at ANY SITE, that that is considered NOT on line?

Not understanding your logic!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[redjackal]

it doesn't have to be 15 minutes. Whatever the number is, it can be assumed that if the person at the computer has not gone to another site in more than 15, 30, or 120 minutes they are away from the computer. As you see from the data, when they are on the computer, there is very little elapsed time from site to site.

 

[SkipVought]

Really?

I sometimes spend a lot more than 15 min at a site, like Tek-Tips, for instance. But maybe I'm not typical.

So what's your logic? I can't access files from work due to security.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[redjackal]

the data has specific page information, so it is many pages within a site as well.

 

[redjackal]

The data consists of more than 9000 records of pages visited within a specific period of time. The data is chronological. I am trying to determine how much time was spent on the web each day and how much time without a break. I choose 15 minutes guessing that 15 minutes or more would indicate that they have walked away from the computer. we don't know for sure but it is a guess. the number can be changed to 1 hour perhaps. The data will be used, hopefully in some graphical way, to show that how much possible sleep and rest the person was getting each day. I can be assumed that if they were on the web continuously, they were not sleeping, resting or eating very well.

 

[xlbo]

As a starter for 10, I would add a column to the end of your data along the following lines:

=if(C2>timevalue("00:15:00"),"Offline","OnLine")

where C2 is your length of time on each page

Copy that down

Put a header in row 1 of the new data that you have just entered - call it UsageType

create a pivot table over your data with dates as your row field and UsageType as column field. SUM of Time as your value

This should give you the total time spent each day either offline or online

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[macropod]

Hi redjackal,

it doesn't have to be 15 minutes. Whatever the number is, it can be assumed that if the person at the computer has not gone to another site in more than 15, 30, or 120 minutes they are away from the computer.

That's just plain nonsense.

I sometimes spend much more than 15 minutes with a single page open, especially:
. forum pages where I might be working on a reply that requires detailed explanation and/or macro coding; and
. pages that contain a great deal of text that one scrolls through to read.

And don't forget how long someone might spend watching a single video or listening to an audio stream on-line.

Somehow I don't think the analysis is going to tell you what you suppose it will. Remember the maxim: Garbage in - Garbage out!


Cheers
[MS MVP - Word]

 

[redjackal]

Thanks XLBO, that did the trick!!!!