Time spent each date by user

[guitardave78]

Sorry already posted this but the subject was usless as a description

I have a sheet with the following headers
username Page Date Time

I need to sort this info and do some maths!

I need to have a sheet that shows the difference between the first time a person views a page and the last time a person views a page on each date

so

abrar page1.asp 17/11/2003 08:12:10
abrar page1.asp 17/11/2003 08:13:03
abrar page1.asp 17/11/2003 08:13:05
abrar page1.asp 17/11/2003 08:13:09
abrar page1.asp 17/11/2003 08:15:45
abrar page2.asp 17/11/2003 08:19:27

gives

abrar 17/11/2003 00:07:17
abrar 18/11/2003 00:53:20
I want it split up by each date and by user
Any ideas?

 

[bluedragon2]

Try this:

date reference is F1
Date Array is D1100
Adjust to your needs:

=INDIRECT(ADDRESS(MATCH(F1,C1:C100),2,4))-INDIRECT(ADDRESS(MATCH(F1,C1:C100,0),2,4))

Format cell for hh:mm:ss



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[guitardave78]

Cant really get it to work, trouble is I have 5000 records with about 200 users so i need somthing a bit more automated

 

[bluedragon2]

Are the 5000 records sorted on anything in paticular?



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[guitardave78]

um by user name
It is the log of people who have used a website, and when they logged in, We need to estimate the amount of time they have spent on the site on each occasion by taking the first time and subtracting it from the last time on each date

 

[xlbo]

should be able to do this with a pivot table
user, page and date as row fields
Create a formula - call it "Min Time". Formula is =Min(Time)
Create another formula - call it "Max Time". Formula is =Max(Time)
Create a final formula "Total Time". Formula is =Max Time - Min Time

Put Sum of Total Time in the Value field and format as hh:mm:ss

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[guitardave78]

looks like it should work, bu the sum of the min time and sum of the max time seem to always be the same? so the total comes out as zero

 

[guitardave78]

I think it is because the pivot table takes the sum of each value as opposed to the sum of the total number of dates, so the max and min time are the same

 

[guitardave78]

Thanks for your help guys, i did it in access using a query that took max and min of times and then subtracted them, this gave me a breakdown by date

 

[xlbo]

b*gger - sorry 'bout that. Theoretically, it should work but I didn't have time to test - too used to working with calculations based on a context - I forget that pivottables aren't that sophisticated

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[bluedragon2]

I guess my post didn't go through the other day, I made a sample Excel sheet that does wht you want if you are interested.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...